﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class AnalyticsReportDA : IAnalyticsReportDA
    {
        public DataSet GetAdjustMentRptDataSet(List<string> sqlWhere, InvAdjustmentRptQueryEntity invAdjustmentRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT A.ADJUSTMENT_ID AS ADJUSTMENT_ID,A.ADJUSTMENT_DATE AS ADJUSTMENT_DATE ,T.[TEXT] AS REASON_CODE,A.OWNER_ID AS OWNER_ID ,A.SKU_ID AS SKU_ID,C.SKU_CLASS_ID AS SKU_CLASS_ID,C.SKU_GROUP AS SKU_GROUP,C.DESCR AS SKU_DESCR,A.QTY AS QTY ,A.PACK_ID AS PACK_ID,D.UOM_DESCR AS UOM_DESCR,B.UNIT_PRICE AS UNIT_PRICE,(A.QTY*B.UNIT_PRICE) AS EXTENDED_PRICE,(C.NET_WGT*A.QTY) AS TOTALWGT,A.LOT AS LOT,A.LOC AS LOC,A.LPN_ID AS LPN_ID,A.REMARK AS REMARK,C.DESCR2 AS SKU_DESCR2,C.UDF2 AS SKU_UDF2,F.PRODUCE_DATE AS PRODUCE_DATE,F.EXPIRY_DATE AS EXPIRY_DATE, F.RECEIPT_DATE AS RECEIPT_DATE,F.LOT AS LOT,F.LOT_ATTR01 AS LOT_ATTR01, F.LOT_ATTR02 AS LOT_ATTR02, F.LOT_ATTR03 AS LOT_ATTR03, F.LOT_ATTR04 AS LOT_ATTR04, F.LOT_ATTR05 AS LOT_ATTR05, F.LOT_ATTR06 AS LOT_ATTR06,F.LOT_ATTR07 AS LOT_ATTR07, F.LOT_ATTR08 AS LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, F.LOT_ATTR10 AS LOT_ATTR10,F.VENDOR_ID AS VENDOR_ID,F.EXTERNAL_LOT AS EXTERNAL_LOT ");
            strSql.Append(" FROM @_@ADJUSTMENT_DETAIL A  INNER JOIN @_@INV_TRANS   B  ON A.TRANS_ID=B.TRANS_ID  INNER JOIN @_@SKU C ON A.SKU_ID=C.SKU_ID AND A.OWNER_ID=C.OWNER_ID  INNER JOIN @_@UOM  D ON D.UOM_ID = A.UOM_ID  INNER JOIN @_@LOT_ATTRIBUTE F ON  A.LOT = F.LOT   LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON F.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = A.REASON_CODE AND SCD.SYS_CODE_TYPE = 'AdjReason' LEFT JOIN sysadm.TRANSLATION T ON 'AdjReason.'+A.REASON_CODE = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID WHERE A.REASON_CODE !='UnReceive'  AND  A.REASON_CODE !='UnShipping' ");
            string sql = string.Empty;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            sql = (strSql.ToString() + sql).Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            if (!invAdjustmentRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, invAdjustmentRptQueryEntity.CurrentPage, invAdjustmentRptQueryEntity.PageSize, invAdjustmentRptQueryEntity.SortField, invAdjustmentRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + invAdjustmentRptQueryEntity.SortField + " " + invAdjustmentRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<ASNStatusRptInfo> GetASNStatusRptInfo(List<string> sqlWhere, ASNStatusRptQueryEntity asnStatusRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSql = " SELECT DISTINCT RECEIPT_ID,PO_ID,T.[TEXT] AS STATUS,T2.[TEXT] AS RECEIPT_TYPE,TEMP.OWNER_ID, EXTERNAL_RECEIPT_ID,RECEIPT_DATE,EXPECTED_RECEIPT_DATE,VENDOR_ID,RMA,TEMP.REMARK,VENDOR_NAME FROM ( SELECT R.RECEIPT_ID,RD.RECEIPT_LINE_ID,R.PO_ID,R.STATUS,R.RECEIPT_TYPE,R.OWNER_ID,RD.SKU_ID,SKU.DESCR,SKU.DESCR2, SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR, R.EXTERNAL_RECEIPT_ID,R.RECEIPT_DATE,R.EXPECTED_RECEIPT_DATE,R.VENDOR_ID,R.VENDOR_NAME,R.RMA,R.REMARK FROM  @_@RECEIPT R LEFT JOIN @_@RECEIPT_DETAIL RD ON RD.RECEIPT_ID = R.RECEIPT_ID LEFT JOIN @_@SKU SKU ON RD.SKU_ID = SKU.SKU_ID AND RD.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@SKU_CLASS SC ON SC.SKU_CLASS_ID = SKU.SKU_CLASS_ID AND SC.OWNER_ID = SKU.OWNER_ID WHERE R.IS_PALLETIZED = 'N' UNION ALL  SELECT R.RECEIPT_ID,RDL.RECEIPT_LINE_ID,R.PO_ID,R.STATUS,R.RECEIPT_TYPE,R.OWNER_ID,RDL.SKU_ID,SKU.DESCR,SKU.DESCR2, SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR, R.EXTERNAL_RECEIPT_ID,R.RECEIPT_DATE,R.EXPECTED_RECEIPT_DATE,R.VENDOR_ID,R.VENDOR_NAME,R.RMA,R.REMARK FROM  @_@RECEIPT R LEFT JOIN @_@RECEIPT_DETAIL_LPN RDL ON RDL.RECEIPT_ID = R.RECEIPT_ID LEFT JOIN @_@SKU SKU ON RDL.SKU_ID = SKU.SKU_ID AND RDL.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@SKU_CLASS SC ON SC.SKU_CLASS_ID = SKU.SKU_CLASS_ID AND SC.OWNER_ID = SKU.OWNER_ID WHERE R.IS_PALLETIZED = 'Y' ) TEMP  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = TEMP.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = TEMP.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+TEMP.STATUS = T.ITEM_ID        AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+TEMP.RECEIPT_TYPE = T2.ITEM_ID        AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON TEMP.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE (VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE [USER_ID] = @USER_ID) OR VENDOR_ID IS NULL OR VENDOR_ID = '') ";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!asnStatusRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, asnStatusRptQueryEntity.CurrentPage, asnStatusRptQueryEntity.PageSize, asnStatusRptQueryEntity.SortField, asnStatusRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + asnStatusRptQueryEntity.SortField + " " + asnStatusRptQueryEntity.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<ASNStatusRptInfo> asnStatusRptInfo = new List<ASNStatusRptInfo>();
            ASNStatusRptInfo asnStatusInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            asnStatusInfo = new ASNStatusRptInfo(ds.Tables[2].Rows[i]["RECEIPT_ID"].ToString(), ds.Tables[2].Rows[i]["PO_ID"].ToString(), ds.Tables[2].Rows[i]["STATUS"].ToString(), ds.Tables[2].Rows[i]["RECEIPT_TYPE"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["EXTERNAL_RECEIPT_ID"].ToString(), (ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[2].Rows[i]["RMA"].ToString(), ds.Tables[2].Rows[i]["REMARK"].ToString(), ds.Tables[2].Rows[i]["VENDOR_NAME"].ToString());
                            asnStatusRptInfo.Add(asnStatusInfo);
                        }
                    }
                    if ((asnStatusRptInfo != null) && (asnStatusRptInfo.Count > 0))
                    {
                        asnStatusRptInfo[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        asnStatusRptInfo[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        asnStatusRptInfo[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return asnStatusRptInfo;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return asnStatusRptInfo;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    asnStatusInfo = new ASNStatusRptInfo(ds.Tables[0].Rows[i]["RECEIPT_ID"].ToString(), ds.Tables[0].Rows[i]["PO_ID"].ToString(), ds.Tables[0].Rows[i]["STATUS"].ToString(), ds.Tables[0].Rows[i]["RECEIPT_TYPE"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["EXTERNAL_RECEIPT_ID"].ToString(), (ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[0].Rows[i]["RMA"].ToString(), ds.Tables[0].Rows[i]["REMARK"].ToString(), ds.Tables[0].Rows[i]["VENDOR_NAME"].ToString());
                    asnStatusRptInfo.Add(asnStatusInfo);
                }
                asnStatusRptInfo[0].CURRENTPAGE = "0";
                asnStatusRptInfo[0].TOTALPAGESCOUNT = "0";
                asnStatusRptInfo[0].TOTALRECORDSCOUNT = "0";
            }
            return asnStatusRptInfo;
        }

        public DataSet GetCustomerReturnDetailRptDataSet(List<string> sqlWhere, CustomerReturnDetailRptQueryEntity customerReturnDetailRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT DISTINCT * FROM (  ");
            strSql.Append(" SELECT RD.RECEIPT_ID,RD.RECEIPT_LINE_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID,T.[TEXT] AS STATUS_R,RD.STATUS AS STATUS_RD,RD.RECEIVED_DATE,R.WAREHOUSE_REF,R.VENDOR_ID,R.VENDOR_NAME,RD.RECEIPT_DATE,");
            strSql.Append(" RD.SKU_ID,SKU.DESCR AS SKU_DESCR,SKU.DESCR2 AS SKU_DESCR2,RD.EXPECTED_QTY,IT.QTY AS RECEIVED_QTY,IT.HOLD_CODE AS CONDITION_CODE,IT.TO_LPN_ID,IT.LOT AS TO_LOT,LAC.DESCR AS SKU_PROPERTY, ");
            strSql.Append(" IT.LOT_ATTR01,IT.LOT_ATTR02,IT.LOT_ATTR03,IT.LOT_ATTR04,IT.LOT_ATTR05,IT.LOT_ATTR06,IT.LOT_ATTR07,IT.LOT_ATTR08,IT.LOT_ATTR09,IT.LOT_ATTR10,IT.PRODUCE_DATE,IT.EXPIRY_DATE,IT.EXTERNAL_LOT,");
            strSql.Append(" RD.REMARK,SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR,SKU.SKU_GROUP,SKU.PACK_ID,SKU.UDF2 AS SKU_UDF2,RD.UNIT_PRICE,ISNULL(IT.QTY * RD.UNIT_PRICE,0) AS PRICE,");
            strSql.Append(" IT.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,IT.QTY * SKU.[CUBE] AS TOTAL_CUBE,IT.QTY * SKU.NET_WGT AS TOTAL_WGT,U.UOM_DESCR ");
            strSql.Append(" FROM @_@RECEIPT_DETAIL RD ");
            strSql.Append(" LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID ");
            strSql.Append(" LEFT JOIN @_@INV_TRANS IT ON RD.RECEIPT_ID = IT.RECEIPT_ID AND RD.RECEIPT_LINE_ID = IT.RECEIPT_LINE_ID ");
            strSql.Append(" AND IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'ReceiptDetailAdd'  AND IT.STATUS = 'OK' ");
            strSql.Append(" LEFT JOIN @_@VENDOR V ON IT.VENDOR_ID = V.VENDOR_ID");
            strSql.Append(" LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID ");
            strSql.Append(" LEFT JOIN  @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID ");
            strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            strSql.Append(" LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID");
            strSql.Append(" LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ");
            strSql.Append(" INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            strSql.Append(" WHERE R.IS_PALLETIZED = 'N' AND R.RECEIPT_TYPE = 'Return' AND RD.[STATUS] IN ('InReceiving', 'ReceivedComplete') ");
            strSql.Append(" UNION ALL  ");
            strSql.Append(" SELECT RD.RECEIPT_ID,RD.RECEIPT_LINE_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID,T.[TEXT] AS STATUS_R,RD.STATUS AS STATUS_RD,RD.RECEIVED_DATE,R.WAREHOUSE_REF,R.VENDOR_ID,R.VENDOR_NAME,RD.RECEIPT_DATE,");
            strSql.Append(" RD.SKU_ID,SKU.DESCR AS SKU_DESCR,SKU.DESCR2 AS SKU_DESCR2,RD.EXPECTED_QTY,IT.QTY AS RECEIVED_QTY,IT.HOLD_CODE AS CONDITION_CODE,IT.TO_LPN_ID,IT.LOT AS TO_LOT,LAC.DESCR AS SKU_PROPERTY, ");
            strSql.Append(" IT.LOT_ATTR01,IT.LOT_ATTR02,IT.LOT_ATTR03,IT.LOT_ATTR04,IT.LOT_ATTR05,IT.LOT_ATTR06,IT.LOT_ATTR07,IT.LOT_ATTR08,IT.LOT_ATTR09,IT.LOT_ATTR10,IT.PRODUCE_DATE,IT.EXPIRY_DATE,IT.EXTERNAL_LOT,");
            strSql.Append(" RD.REMARK,SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR,SKU.SKU_GROUP,SKU.PACK_ID,SKU.UDF2 AS SKU_UDF2,RD.UNIT_PRICE,ISNULL(IT.QTY * RD.UNIT_PRICE,0) AS PRICE,");
            strSql.Append(" IT.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,IT.QTY * SKU.[CUBE] AS TOTAL_CUBE,IT.QTY * SKU.NET_WGT AS TOTAL_WGT,U.UOM_DESCR ");
            strSql.Append(" FROM @_@RECEIPT_DETAIL_LPN RD  ");
            strSql.Append(" LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID ");
            strSql.Append(" LEFT JOIN @_@INV_TRANS IT ON RD.RECEIPT_ID = IT.RECEIPT_ID AND RD.RECEIPT_LINE_ID = IT.RECEIPT_LINE_ID ");
            strSql.Append(" AND IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'ReceiptDetailAdd'  AND IT.STATUS = 'OK' ");
            strSql.Append(" LEFT JOIN @_@VENDOR V ON IT.VENDOR_ID = V.VENDOR_ID");
            strSql.Append(" LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID ");
            strSql.Append(" LEFT JOIN  @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID ");
            strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            strSql.Append(" LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID");
            strSql.Append(" LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID AND T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ");
            strSql.Append(" INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            strSql.Append(" WHERE R.IS_PALLETIZED = 'Y' AND R.RECEIPT_TYPE = 'Return' AND RD.[STATUS] IN ('InReceiving', 'ReceivedComplete') ");
            strSql.Append(" ) TEMP WHERE (VENDOR_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR VENDOR_ID IS NULL OR VENDOR_ID = '')");
            string sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!customerReturnDetailRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, customerReturnDetailRptQueryEntity.CurrentPage, customerReturnDetailRptQueryEntity.PageSize, customerReturnDetailRptQueryEntity.SortField, customerReturnDetailRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + customerReturnDetailRptQueryEntity.SortField + " " + customerReturnDetailRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<DiffCountRptInfo> GetDiffCountReportList(List<string> sqlWhere, DiffCountRptQueryEntity diffCountRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT DISTINCT tmp.LOC, tmp.OWNER_ID, tmp.SKU_ID, tmp.SKU_DESCR, tmp.SKU_DESCR2, tmp.SKU_STYLE, tmp.UOM_ID, tmp.LPN_ID, SUM(INV_QTY) AS INV_QTY ");
            if (diffCountRptQueryEntity.ShowLot == "Y")
            {
                strSql.Append(" , LOT, EXTERNAL_LOT, PRODUCE_DATE, EXPIRY_DATE, VENDOR_ID, RECEIPT_DATE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10");
            }
            strSql.Append(" FROM ( ");
            strSql.Append(" SELECT DISTINCT ILLL.LOC, ILLL.OWNER_ID, ILLL.SKU_ID, ILLL.LPN_ID, ILLL.LOT, ILLL.QTY AS INV_QTY, S.DESCR AS SKU_DESCR,S.DESCR2 AS SKU_DESCR2, P.PACK_ID AS SKU_STYLE, U.UOM_DESCR AS UOM_ID, LA.EXTERNAL_LOT, LA.PRODUCE_DATE, LA.EXPIRY_DATE,LA.VENDOR_ID, LA.RECEIPT_DATE, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05,LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, LA.LOT_ATTR10 ");
            strSql.Append(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            strSql.Append(" LEFT JOIN @_@LOCATION L ON ILLL.LOC=L.LOC ");
            strSql.Append(" LEFT JOIN @_@SKU S ON S.OWNER_ID = ILLL.OWNER_ID AND S.SKU_ID = ILLL.SKU_ID ");
            strSql.Append(" LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID ");
            strSql.Append(" LEFT JOIN @_@UOM U ON P.PACK_UOM1=U.UOM_ID ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = ILLL.LOT ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            if (diffCountRptQueryEntity.ShowOtherSku == "Y")
            {
                strSql.Append(" LEFT JOIN @_@INV_TRANS IT ON (IT.FROM_LOC = ILLL.LOC OR IT.TO_LOC = ILLL.LOC) ");
            }
            else
            {
                strSql.Append(" LEFT JOIN @_@INV_TRANS IT ON IT.OWNER_ID = ILLL.OWNER_ID AND IT.SKU_ID = ILLL.SKU_ID AND (IT.FROM_LOC = ILLL.LOC OR IT.TO_LOC = ILLL.LOC) ");
            }
            strSql.Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSql.Append(" WHERE " + sqlWhere[i].ToString());
                }
                else
                {
                    strSql.Append(" AND " + sqlWhere[i].ToString());
                }
            }
            strSql.Append(" ) tmp ");
            strSql.Append(" INNER JOIN SYSADM.USER_OWNER UO ON tmp.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID");
            strSql.Append(" GROUP BY tmp.LOC, tmp.OWNER_ID, tmp.SKU_ID, tmp.SKU_DESCR, tmp.SKU_DESCR2, tmp.SKU_STYLE, tmp.UOM_ID, tmp.LPN_ID ");
            if (diffCountRptQueryEntity.ShowLot == "Y")
            {
                strSql.Append(" , LOT, EXTERNAL_LOT, PRODUCE_DATE, EXPIRY_DATE, VENDOR_ID, RECEIPT_DATE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10");
            }
            string sql = strSql.ToString();
            if (!diffCountRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, diffCountRptQueryEntity.CurrentPage, diffCountRptQueryEntity.PageSize, diffCountRptQueryEntity.SortField, diffCountRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + diffCountRptQueryEntity.SortField + " " + diffCountRptQueryEntity.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<DiffCountRptInfo> diffCountRptInfoList = new List<DiffCountRptInfo>();
            DiffCountRptInfo diffCountRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            if (diffCountRptQueryEntity.ShowLot == "Y")
                            {
                                diffCountRptInfo = new DiffCountRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR2"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[2].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[2].Rows[i]["UOM_ID"].ToString(), ds.Tables[2].Rows[i]["LPN_ID"].ToString(), ds.Tables[2].Rows[i]["LOT"].ToString(), ds.Tables[2].Rows[i]["EXTERNAL_LOT"].ToString(), (ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["VENDOR_ID"].ToString(), (ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR10"].ToString());
                            }
                            else
                            {
                                diffCountRptInfo = new DiffCountRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR2"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[2].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[2].Rows[i]["UOM_ID"].ToString(), ds.Tables[2].Rows[i]["LPN_ID"].ToString());
                            }
                            diffCountRptInfoList.Add(diffCountRptInfo);
                        }
                    }
                    if ((diffCountRptInfoList != null) && (diffCountRptInfoList.Count > 0))
                    {
                        diffCountRptInfoList[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        diffCountRptInfoList[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        diffCountRptInfoList[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return diffCountRptInfoList;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return diffCountRptInfoList;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (diffCountRptQueryEntity.ShowLot == "Y")
                    {
                        diffCountRptInfo = new DiffCountRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR2"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[0].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[0].Rows[i]["UOM_ID"].ToString(), ds.Tables[0].Rows[i]["LPN_ID"].ToString(), ds.Tables[0].Rows[i]["LOT"].ToString(), ds.Tables[0].Rows[i]["EXTERNAL_LOT"].ToString(), (ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["VENDOR_ID"].ToString(), (ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR10"].ToString());
                    }
                    else
                    {
                        diffCountRptInfo = new DiffCountRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR2"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[0].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[0].Rows[i]["UOM_ID"].ToString(), ds.Tables[0].Rows[i]["LPN_ID"].ToString());
                    }
                    diffCountRptInfoList.Add(diffCountRptInfo);
                }
                diffCountRptInfoList[0].CURRENTPAGE = "0";
                diffCountRptInfoList[0].TOTALPAGESCOUNT = "0";
                diffCountRptInfoList[0].TOTALRECORDSCOUNT = "0";
            }
            return diffCountRptInfoList;
        }

        public List<EmptyLocationRptInfo> GetEmptyLocationRptInfo(List<string> sqlWhere, EmptyLocationRptQueryEntity emptyLocationRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string sql = string.Empty;
            sql = "SELECT DISTINCT * FROM (  SELECT LOC.LOC,LOC.ZONE_ID,  LOC.LOC_USAGE,  T.[TEXT] AS T_LOC_USAGE,  LOC.ABC,  T3.[TEXT] AS T_ABC,  LOC.STATUS,  T2.[TEXT] AS T_STATUS,  LOC.LOC_FLAG,  T1.[TEXT] AS T_LOC_FLAG,  LOC.LOGICAL_LOC,LOC.IS_MULTI_SKU,LOC.IS_MULTI_LOT,LOC.CHECK_DIG,LOC.CUBIC_CAPACITY,LOC.WEIGHT_CAPACITY   FROM @_@LOCATION as LOC  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = LOC.LOC_USAGE AND SCD.SYS_CODE_TYPE = 'LocUsage'  LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = LOC.LOC_FLAG AND SCD2.SYS_CODE_TYPE = 'LocFlag'  LEFT JOIN @_@SYS_CODE_DETAIL SCD3 ON SCD3.SYS_CODE = LOC.STATUS AND SCD3.SYS_CODE_TYPE = 'LocFlag'  LEFT JOIN @_@SYS_CODE_DETAIL SCD4 ON SCD4.SYS_CODE = LOC.ABC AND SCD3.SYS_CODE_TYPE = 'ABC'  LEFT JOIN sysadm.TRANSLATION T ON 'LocUsage.'+ LOC.LOC_USAGE = T.ITEM_ID         AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE   LEFT JOIN sysadm.TRANSLATION T1 ON 'LocFlag.'+LOC.LOC_FLAG = T1.ITEM_ID     AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T2 ON 'LocFlag.'+LOC.STATUS = T2.ITEM_ID    AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T3 ON 'ABC.'+LOC.ABC = T3.ITEM_ID     AND  T3.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T3.LANG+'-'+T3.COUNTRY = @LANGUAGE  WHERE LOC.LOC NOT IN (SELECT LOC FROM @_@INV_SKU_LOC)   ) TEMP  ";
            sql = sql.Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!emptyLocationRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, emptyLocationRptQuery.CurrentPage, emptyLocationRptQuery.PageSize, emptyLocationRptQuery.SortField, emptyLocationRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + emptyLocationRptQuery.SortField + " " + emptyLocationRptQuery.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<EmptyLocationRptInfo> emptyLocationRptInfos = new List<EmptyLocationRptInfo>();
            EmptyLocationRptInfo emptyLocationRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            emptyLocationRptInfo = new EmptyLocationRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["ZONE_ID"].ToString(), ds.Tables[2].Rows[i]["LOC_USAGE"].ToString(), ds.Tables[2].Rows[i]["ABC"].ToString(), ds.Tables[2].Rows[i]["STATUS"].ToString(), ds.Tables[2].Rows[i]["LOGICAL_LOC"].ToString(), ds.Tables[2].Rows[i]["LOC_FLAG"].ToString(), ds.Tables[2].Rows[i]["IS_MULTI_SKU"].ToString(), ds.Tables[2].Rows[i]["IS_MULTI_LOT"].ToString(), ds.Tables[2].Rows[i]["CHECK_DIG"].ToString(), (ds.Tables[2].Rows[i]["CUBIC_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[2].Rows[i]["CUBIC_CAPACITY"].ToString()) : 0f, (ds.Tables[2].Rows[i]["WEIGHT_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[2].Rows[i]["WEIGHT_CAPACITY"].ToString()) : 0f, ds.Tables[2].Rows[i]["T_LOC_USAGE"].ToString(), ds.Tables[2].Rows[i]["T_ABC"].ToString(), ds.Tables[2].Rows[i]["T_STATUS"].ToString(), ds.Tables[2].Rows[i]["T_LOC_FLAG"].ToString());
                            emptyLocationRptInfos.Add(emptyLocationRptInfo);
                        }
                    }
                    if ((emptyLocationRptInfos != null) && (emptyLocationRptInfos.Count > 0))
                    {
                        emptyLocationRptInfos[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        emptyLocationRptInfos[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        emptyLocationRptInfos[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return emptyLocationRptInfos;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return emptyLocationRptInfos;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    emptyLocationRptInfo = new EmptyLocationRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["ZONE_ID"].ToString(), ds.Tables[0].Rows[i]["LOC_USAGE"].ToString(), ds.Tables[0].Rows[i]["ABC"].ToString(), ds.Tables[0].Rows[i]["STATUS"].ToString(), ds.Tables[0].Rows[i]["LOGICAL_LOC"].ToString(), ds.Tables[0].Rows[i]["LOC_FLAG"].ToString(), ds.Tables[0].Rows[i]["IS_MULTI_SKU"].ToString(), ds.Tables[0].Rows[i]["IS_MULTI_LOT"].ToString(), ds.Tables[0].Rows[i]["CHECK_DIG"].ToString(), (ds.Tables[0].Rows[i]["CUBIC_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[0].Rows[i]["CUBIC_CAPACITY"].ToString()) : 0f, (ds.Tables[0].Rows[i]["WEIGHT_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[0].Rows[i]["WEIGHT_CAPACITY"].ToString()) : 0f, ds.Tables[0].Rows[i]["T_LOC_USAGE"].ToString(), ds.Tables[0].Rows[i]["T_ABC"].ToString(), ds.Tables[0].Rows[i]["T_STATUS"].ToString(), ds.Tables[0].Rows[i]["T_LOC_FLAG"].ToString());
                    emptyLocationRptInfos.Add(emptyLocationRptInfo);
                }
                emptyLocationRptInfos[0].CURRENTPAGE = "0";
                emptyLocationRptInfos[0].TOTALPAGESCOUNT = "0";
                emptyLocationRptInfos[0].TOTALRECORDSCOUNT = "0";
            }
            return emptyLocationRptInfos;
        }

        public DataSet GetFinanceInOutStockDetailRptDataSet(List<string> sqlWhere, FinanceInOutStockDetailRptQueryEntity financeInOutStockDetailRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            if (financeInOutStockDetailRptQueryEntity.ShowLot == "Y")
            {
                sql = "SELECT DISTINCT IL.OWNER_ID,IL.SKU_ID,SKU.DESCR,SKU.DESCR2,SKU.UDF2 AS SKU_UDF2,SKU.SKU_GROUP,UOM.UOM_DESCR,SC.DESCR AS SKU_CLASS_DESCR, ISNULL(IL.UNIT_PRICE,0) AS UNIT_PRICE,ISNULL(INIT.INIT_QTY,0) AS INIT_QTY, ISNULL(IL.UNIT_PRICE,0) AS INIT_PRICE,ISNULL(IL.UNIT_PRICE,0)*ISNULL(INIT.INIT_QTY,0) AS INIT_AMOUNT,  ISNULL(IB.IN_QTY,0) AS IN_QTY,ISNULL(IB.IN_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS IN_AMOUNT, ISNULL(OB.OUT_QTY,0) AS OUT_QTY,ISNULL(OB.OUT_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS OUT_AMOUNT, ISNULL(AJ.AJ_QTY,0) AS AJ_QTY,ISNULL(AJ.AJ_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS AJ_AMOUNT,  (ISNULL(INIT.INIT_QTY,0) + ISNULL(IB.IN_QTY,0) - ISNULL(OB.OUT_QTY,0) + ISNULL(AJ.AJ_QTY,0)) AS END_QTY, ISNULL(IL.UNIT_PRICE,0) AS END_PRICE,(ISNULL(INIT.INIT_QTY,0) + ISNULL(IB.IN_QTY,0) - ISNULL(OB.OUT_QTY,0) + ISNULL(AJ.AJ_QTY,0))*ISNULL(IL.UNIT_PRICE,0) AS END_AMOUNT,  LA.EXTERNAL_LOT,LA.VENDOR_ID,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08, LA.LOT_ATTR09,LA.LOT_ATTR10 FROM @_@INV_LOT IL  LEFT JOIN ( SELECT IT1.OWNER_ID, IT1.SKU_ID, IT1.LOT,SUM(IT1.QTY) - ABS(ISNULL(IT2.QTY,0)) + ISNULL(IT_AJ.QTY,0) AS INIT_QTY FROM @_@INV_TRANS IT1 FULL JOIN ( SELECT OWNER_ID,SKU_ID,LOT,SUM(QTY) AS QTY FROM @_@INV_TRANS WHERE TRANS_TYPE = 'OT' AND SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND STATUS = 'OK' AND TRANS_DATE < @BEGIN_DATE GROUP BY OWNER_ID,SKU_ID,LOT ) IT2 ON IT1.OWNER_ID = IT2.OWNER_ID AND IT1.SKU_ID = IT2.SKU_ID AND IT1.LOT = IT2.LOT FULL JOIN ( SELECT OWNER_ID,SKU_ID,LOT,SUM(QTY) AS QTY FROM @_@INV_TRANS WHERE TRANS_TYPE IN ('AJ','MV') AND SOURCE_TRANS_TYPE IN ('AdjustmentDetailAdd','CycleCountAdd','TransferDetailAdd','TransferDetailUpdate') AND STATUS = 'OK' AND TRANS_DATE < @BEGIN_DATE GROUP BY OWNER_ID,SKU_ID,LOT ) IT_AJ ON IT1.OWNER_ID = IT_AJ.OWNER_ID AND IT1.SKU_ID = IT_AJ.SKU_ID AND IT1.LOT = IT_AJ.LOT WHERE IT1.TRANS_TYPE = 'IN' AND IT1.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT1.STATUS = 'OK' AND RECEIPT_DATE < @BEGIN_DATE GROUP BY IT1.OWNER_ID, IT1.SKU_ID,IT1.LOT,IT2.QTY,IT_AJ.QTY ) INIT ON IL.OWNER_ID = INIT.OWNER_ID AND IL.SKU_ID = INIT.SKU_ID AND INIT.LOT = IL.LOT LEFT JOIN  ( SELECT IT.OWNER_ID,IT.SKU_ID,IT.LOT,SUM(IT.QTY) AS IN_QTY FROM @_@INV_TRANS IT LEFT JOIN @_@RECEIPT R ON IT.DOC_ID = R.RECEIPT_ID WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.STATUS = 'OK' AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.LOT  ) IB ON IL.OWNER_ID = IB.OWNER_ID AND IL.SKU_ID = IB.SKU_ID AND IL.LOT= IB.LOT LEFT JOIN ( SELECT IT.OWNER_ID,IT.SKU_ID,IT.LOT,ABS(SUM(IT.QTY)) AS OUT_QTY FROM @_@INV_TRANS IT LEFT JOIN @_@ORDERS O ON IT.DOC_ID = O.ORDER_ID WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.STATUS = 'OK' AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.LOT ) OB ON IL.OWNER_ID = OB.OWNER_ID AND IL.SKU_ID = OB.SKU_ID AND IL.LOT = OB.LOT LEFT JOIN (  SELECT IT.OWNER_ID,IT.SKU_ID,IT.LOT,SUM(IT.QTY) AS AJ_QTY   FROM @_@INV_TRANS IT  WHERE IT.[STATUS] = 'OK' AND IT.TRANS_TYPE IN ('AJ','MV') AND IT.SOURCE_TRANS_TYPE IN ('AdjustmentDetailAdd','CycleCountAdd','TransferDetailAdd','TransferDetailUpdate')  AND IT.TRANS_DATE BETWEEN @BEGIN_DATE AND @END_DATE  GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.LOT  ) AJ ON IL.OWNER_ID = AJ.OWNER_ID AND IL.SKU_ID = AJ.SKU_ID AND IL.LOT = AJ.LOT  LEFT JOIN @_@SKU SKU ON IL.OWNER_ID = SKU.OWNER_ID AND IL.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON SKU.OWNER_ID = SC.OWNER_ID AND SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID  LEFT JOIN @_@PACK P ON SKU.PACK_ID = P.PACK_ID  LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = P.PACK_UOM1  LEFT JOIN @_@LOT_ATTRIBUTE LA  ON IL.LOT = LA.LOT INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = IL.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
            }
            else
            {
                sql = "SELECT DISTINCT IL.OWNER_ID,IL.SKU_ID,SKU.DESCR,SKU.DESCR2,SKU.UDF2 AS SKU_UDF2,SKU.SKU_GROUP,UOM.UOM_DESCR,SC.DESCR AS SKU_CLASS_DESCR, ISNULL(IL.UNIT_PRICE,0) AS UNIT_PRICE,ISNULL(INIT.INIT_QTY,0) AS INIT_QTY, ISNULL(IL.UNIT_PRICE,0) AS INIT_PRICE,ISNULL(IL.UNIT_PRICE,0)*ISNULL(INIT.INIT_QTY,0) AS INIT_AMOUNT,  ISNULL(IB.IN_QTY,0) AS IN_QTY,ISNULL(IB.IN_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS IN_AMOUNT, ISNULL(OB.OUT_QTY,0) AS OUT_QTY,ISNULL(OB.OUT_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS OUT_AMOUNT, ISNULL(AJ.AJ_QTY,0) AS AJ_QTY,ISNULL(AJ.AJ_QTY,0)*ISNULL(IL.UNIT_PRICE,0) AS AJ_AMOUNT,  (ISNULL(INIT.INIT_QTY,0) + ISNULL(IB.IN_QTY,0) - ISNULL(OB.OUT_QTY,0) + ISNULL(AJ.AJ_QTY,0)) AS END_QTY, ISNULL(IL.UNIT_PRICE,0) AS END_PRICE,(ISNULL(INIT.INIT_QTY,0) + ISNULL(IB.IN_QTY,0) - ISNULL(OB.OUT_QTY,0) + ISNULL(AJ.AJ_QTY,0))*ISNULL(IL.UNIT_PRICE,0) AS END_AMOUNT  FROM @_@INV_LOT IL  LEFT JOIN  ( SELECT IT1.OWNER_ID, IT1.SKU_ID, IT1.UNIT_PRICE,SUM(IT1.QTY) - ABS(ISNULL(IT2.QTY,0)) + ISNULL(IT_AJ.QTY,0) AS INIT_QTY FROM @_@INV_TRANS IT1 FULL JOIN ( SELECT OWNER_ID,SKU_ID,UNIT_PRICE,SUM(QTY) AS QTY FROM @_@INV_TRANS WHERE TRANS_TYPE = 'OT' AND SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND STATUS = 'OK' AND TRANS_DATE < @BEGIN_DATE GROUP BY OWNER_ID,SKU_ID,UNIT_PRICE ) IT2 ON IT1.OWNER_ID = IT2.OWNER_ID AND IT1.SKU_ID = IT2.SKU_ID AND IT1.UNIT_PRICE = IT2.UNIT_PRICE FULL JOIN ( SELECT OWNER_ID,SKU_ID,UNIT_PRICE,SUM(QTY) AS QTY FROM @_@INV_TRANS WHERE TRANS_TYPE IN ('AJ','MV') AND SOURCE_TRANS_TYPE IN ('AdjustmentDetailAdd','CycleCountAdd','TransferDetailAdd','TransferDetailUpdate') AND STATUS = 'OK' AND TRANS_DATE < @BEGIN_DATE GROUP BY OWNER_ID,SKU_ID,UNIT_PRICE ) IT_AJ ON IT1.OWNER_ID = IT_AJ.OWNER_ID AND IT1.SKU_ID = IT_AJ.SKU_ID AND IT1.UNIT_PRICE = IT_AJ.UNIT_PRICE WHERE IT1.TRANS_TYPE = 'IN' AND IT1.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT1.STATUS = 'OK' AND RECEIPT_DATE < @BEGIN_DATE GROUP BY IT1.OWNER_ID, IT1.SKU_ID,IT1.UNIT_PRICE,IT2.QTY,IT_AJ.QTY ) INIT ON IL.OWNER_ID = INIT.OWNER_ID AND IL.SKU_ID = INIT.SKU_ID AND IL.UNIT_PRICE = INIT.UNIT_PRICE LEFT JOIN  ( SELECT IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE,SUM(IT.QTY) AS IN_QTY FROM @_@INV_TRANS IT LEFT JOIN @_@RECEIPT R ON IT.DOC_ID = R.RECEIPT_ID WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND IT.STATUS = 'OK' AND R.RECEIPT_TYPE <> 'RETURN' AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE ) IB ON IL.OWNER_ID = IB.OWNER_ID AND IL.SKU_ID = IB.SKU_ID AND IL.UNIT_PRICE = IB.UNIT_PRICE LEFT JOIN ( SELECT IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE,ABS(SUM(IT.QTY)) AS OUT_QTY FROM @_@INV_TRANS IT LEFT JOIN @_@ORDERS O ON IT.DOC_ID = O.ORDER_ID WHERE IT.TRANS_TYPE = 'OT' AND IT.SOURCE_TRANS_TYPE = 'PICKINGUPDATE' AND IT.STATUS = 'OK' AND O.TYPE <> 'RETURN' AND IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE ) OB ON IL.OWNER_ID = OB.OWNER_ID AND IL.SKU_ID = OB.SKU_ID AND IL.UNIT_PRICE = OB.UNIT_PRICE LEFT JOIN (  SELECT IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE,SUM(IT.QTY) AS AJ_QTY   FROM @_@INV_TRANS IT  WHERE IT.[STATUS] = 'OK' AND IT.TRANS_TYPE IN ('AJ','MV') AND IT.SOURCE_TRANS_TYPE IN ('AdjustmentDetailAdd','CycleCountAdd','TransferDetailAdd','TransferDetailUpdate')  AND IT.TRANS_DATE BETWEEN @BEGIN_DATE AND @END_DATE  GROUP BY IT.OWNER_ID,IT.SKU_ID,IT.UNIT_PRICE  ) AJ ON IL.OWNER_ID = AJ.OWNER_ID AND IL.SKU_ID = AJ.SKU_ID AND IL.UNIT_PRICE = AJ.UNIT_PRICE  LEFT JOIN @_@SKU SKU ON IL.OWNER_ID = SKU.OWNER_ID AND IL.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON SKU.OWNER_ID = SC.OWNER_ID AND SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID  LEFT JOIN @_@PACK P ON SKU.PACK_ID = P.PACK_ID  LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = P.PACK_UOM1  LEFT JOIN @_@LOT_ATTRIBUTE LA ON IL.LOT = LA.LOT  INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = IL.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
            }
            sql = sql.Replace("@BEGIN_DATE", "'" + financeInOutStockDetailRptQueryEntity.StartTime + "'").Replace("@END_DATE", "'" + financeInOutStockDetailRptQueryEntity.EndTime + "'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            sql = sql.Replace("@_@", whLoginID).ToString().Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            if (!financeInOutStockDetailRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql.ToString(), financeInOutStockDetailRptQueryEntity.CurrentPage, financeInOutStockDetailRptQueryEntity.PageSize, financeInOutStockDetailRptQueryEntity.SortField, financeInOutStockDetailRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + financeInOutStockDetailRptQueryEntity.SortField + " " + financeInOutStockDetailRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetFinanceSalesDetailByDateRptDataSet(FinanceSalesDetailRptQueryEntiry financeSalesDetailRptQueryEntiry, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder strSelectSql = new StringBuilder();
            strSelectSql.Append(" DECLARE @SQL VARCHAR(8000) ");
            strSelectSql.Append(" DECLARE @STARTTIME DATETIME ");
            strSelectSql.Append(" DECLARE @ENDTIME DATETIME ");
            strSelectSql.AppendFormat("  SELECT @STARTTIME=CONVERT(DATETIME,'{0}') ", financeSalesDetailRptQueryEntiry.StartDatetime);
            strSelectSql.AppendFormat("  SELECT @ENDTIME=CONVERT(DATETIME,'{0}') ", financeSalesDetailRptQueryEntiry.EndDatetime);
            strSelectSql.Append("  IF OBJECT_ID('TEMPDB.DBO.##TimeTable') IS NOT NULL  ");
            strSelectSql.Append("  BEGIN  DROP TABLE ##TimeTable  END ");
            strSelectSql.Append("  SELECT  @STARTTIME as TIME INTO ##TimeTable ");
            strSelectSql.Append("  WHILE  @STARTTIME<@ENDTIME ");
            strSelectSql.Append("  BEGIN   SET @STARTTIME=dateadd(day,1,@STARTTIME)  ");
            strSelectSql.Append("  INSERT ##TimeTable (TIME)VALUES(Convert(varchar,@STARTTIME))  END ");
            strSelectSql.Append("  SET @SQL = 'SELECT OWNER_ID,SKU_ID,SKU_DESCR,PACK_ID ,UOM_DESCR ' ");
            strSelectSql.Append("  SELECT @SQL = @SQL + ',SUM([' + substring(CONVERT(VARCHAR,TIME,102),6,5) + ']) AS  [' + substring(CONVERT(VARCHAR,TIME,102),6,5) + ']' FROM  ##TimeTable TT ORDER BY TIME ");
            strSelectSql.Append("  SET @SQL =@SQL+' FROM ( SELECT ' ");
            strSelectSql.Append("  SELECT @SQL = @SQL + 'CASE  '''+SUBSTRING(CONVERT(VARCHAR,TT.TIME,102),0,11)+'''  WHEN  SUBSTRING(CONVERT(VARCHAR,TEMP.TIMEDATE,102),0,11) THEN sum(TEMP.QTY) ELSE 0 END [' + substring(CONVERT(VARCHAR,TIME,102),6,5) + '],' FROM  ##TimeTable TT ORDER BY TIME ");
            strSelectSql.Append("  SET @SQL = @SQL+'TEMP.SKU_ID,TEMP.OWNER_ID,TEMP.SKU_DESCR,TEMP.PACK_ID,TEMP.UOM_DESCR  FROM (  ");
            strSelectSql.Append("  SELECT SUM(OD.SHIPPED_QTY) AS QTY, SUBSTRING(CONVERT(VARCHAR,OD.ACTUAL_SHIP_DATE,102),0,11)  AS TIMEDATE,OD.SKU_ID,OD.OWNER_ID,S.DESCR AS SKU_DESCR ,S.PACK_ID,U.UOM_DESCR    FROM @_@ORDER_DETAIL OD");
            strSelectSql.Append("  LEFT JOIN @_@ORDERS O ON OD.ORDER_ID =O.ORDER_ID ");
            strSelectSql.Append("  LEFT JOIN @_@SKU S ON OD.OWNER_ID=S.OWNER_ID AND OD.SKU_ID =S.SKU_ID ");
            strSelectSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
            strSelectSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            strSelectSql.AppendFormat("  WHERE   O.TYPE !=''TRANSFER'' AND O.TYPE !=''EXCHANGE'' AND OD.STATUS=''SHIPPED'' AND OD.ACTUAL_SHIP_DATE  BETWEEN ''{0}''  AND ''{1}'' ", financeSalesDetailRptQueryEntiry.StartDatetime, financeSalesDetailRptQueryEntiry.EndDatetime);
            if (!string.IsNullOrEmpty(financeSalesDetailRptQueryEntiry.OwnerID))
            {
                strSelectSql.AppendFormat(" AND OD.OWNER_ID {0}", financeSalesDetailRptQueryEntiry.OwnerID.Replace("'", "''"));
            }
            if (!string.IsNullOrEmpty(financeSalesDetailRptQueryEntiry.SkuID))
            {
                strSelectSql.AppendFormat(" AND OD.SKU_ID {0}", financeSalesDetailRptQueryEntiry.SkuID.Replace("'", "''"));
            }
            if (!string.IsNullOrEmpty(financeSalesDetailRptQueryEntiry.CustomerID))
            {
                strSelectSql.AppendFormat(" AND O.CONSIGNEE_ID {0}", financeSalesDetailRptQueryEntiry.CustomerID.Replace("'", "''"));
            }
            strSelectSql.Append("  GROUP BY SUBSTRING(CONVERT(VARCHAR,OD.ACTUAL_SHIP_DATE,102),0,11),OD.SKU_ID,OD.OWNER_ID,S.DESCR,S.PACK_ID,U.UOM_DESCR    ");
            strSelectSql.Append("  UNION ALL   ");
            strSelectSql.Append("  SELECT SUM(RD.RECEIVED_QTY)*-1 AS QTY,  SUBSTRING(CONVERT(VARCHAR,RD.RECEIVED_DATE,102),0,11)AS TIMEDATE,RD.SKU_ID,RD.OWNER_ID,S.DESCR AS SKU_DESCR,S.PACK_ID,U.UOM_DESCR    FROM  @_@RECEIPT_DETAIL RD ");
            strSelectSql.Append("  LEFT JOIN @_@RECEIPT R  ON RD.RECEIPT_ID=R.RECEIPT_ID ");
            strSelectSql.Append("  LEFT JOIN @_@SKU S ON RD.OWNER_ID=S.OWNER_ID AND RD.SKU_ID =S.SKU_ID ");
            strSelectSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
            strSelectSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            strSelectSql.AppendFormat("  WHERE R.RECEIPT_TYPE =''RETURN'' AND RD.STATUS =''RECEIVEDCOMPLETE''  AND RD.RECEIVED_DATE  BETWEEN ''{0}''  AND ''{1}''  ", financeSalesDetailRptQueryEntiry.StartDatetime, financeSalesDetailRptQueryEntiry.EndDatetime);
            if (!string.IsNullOrEmpty(financeSalesDetailRptQueryEntiry.OwnerID))
            {
                strSelectSql.AppendFormat(" AND RD.OWNER_ID {0}", financeSalesDetailRptQueryEntiry.OwnerID.Replace("'", "''"));
            }
            if (!string.IsNullOrEmpty(financeSalesDetailRptQueryEntiry.SkuID))
            {
                strSelectSql.AppendFormat(" AND RD.SKU_ID {0}", financeSalesDetailRptQueryEntiry.SkuID.Replace("'", "''"));
            }
            strSelectSql.Append(" GROUP BY  SUBSTRING(CONVERT(VARCHAR,RD.RECEIVED_DATE,102),0,11),RD.SKU_ID,RD.OWNER_ID,S.DESCR,S.PACK_ID,U.UOM_DESCR ) TEMP ");
            strSelectSql.Append(" GROUP BY TIMEDATE,SKU_ID,OWNER_ID,SKU_DESCR,TEMP.PACK_ID,TEMP.UOM_DESCR  ");
            strSelectSql.Append(" ) TEMP1 GROUP BY SKU_ID,OWNER_ID,SKU_DESCR,PACK_ID,UOM_DESCR '");
            strSelectSql.Append("  IF OBJECT_ID('TEMPDB.DBO.##TimeTable') IS NOT NULL  ");
            strSelectSql.Append("  BEGIN   DROP TABLE ##TimeTable  END");
            strSelectSql.Append("  EXEC(@SQL) ");
            return DBHelper.ExecuteDataSet(CommandType.Text, strSelectSql.ToString().Replace("@_@", whLoginID));
        }

        public DataSet GetFinanceSalesDetailRptDataSet(List<string> sqlWhere, FinanceSalesDetailRptQueryEntiry financeSalesDetailRptQueryEntiry, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder strSelectSql = new StringBuilder();
            if (financeSalesDetailRptQueryEntiry.SHOW_DATE == "N")
            {
                strSelectSql.Append("SELECT DISTINCT OD.OWNER_ID, OD.SKU_ID, OD.UOM_ID, OD.UNIT_PRICE, OD.REMARK,  S.DESCR AS SKU_DESCR, S.DESCR2 AS SKU_DESCR2, UOM.UOM_DESCR,S.PACK_ID,");
                strSelectSql.Append("  SUM(OD.SHIPPED_QTY) AS QTY, SUM(OD.EXTENDED_PRICE) AS EXTENDED_PRICE");
                strSelectSql.AppendFormat(" FROM {0}ORDER_DETAIL OD LEFT JOIN {0}ORDERS [OR] ON (OD.ORDER_ID = [OR].ORDER_ID)", whLoginID);
                strSelectSql.AppendFormat("  LEFT JOIN {0}SKU S ON (OD.OWNER_ID = S.OWNER_ID) AND (OD.SKU_ID = S.SKU_ID)", whLoginID);
                strSelectSql.AppendFormat("  LEFT JOIN {0}UOM UOM ON (OD.UOM_ID = UOM.UOM_ID)", whLoginID);
                strSelectSql.AppendFormat("   INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = '{0}' ", pubParasInfo.UserID);
                strSelectSql.Append(" WHERE  ( [OD].[STATUS] = 'Shipped' OR [OD].[STATUS] ='PartShipped' )");
            }
            else
            {
                strSelectSql.Append("SELECT DISTINCT OD.OWNER_ID, OD.SKU_ID, OD.UOM_ID, OD.UNIT_PRICE, OD.REMARK,  S.DESCR AS SKU_DESCR, S.DESCR2 AS SKU_DESCR2, UOM.UOM_DESCR,");
                strSelectSql.Append("  SUM(OD.SHIPPED_QTY) AS QTY, SUM(OD.EXTENDED_PRICE) AS EXTENDED_PRICE,Convert(varchar,[OR].ACTUAL_SHIP_DATE,102) AS ACTUAL_SHIP_DATE,S.PACK_ID ");
                strSelectSql.AppendFormat(" FROM {0}ORDER_DETAIL OD LEFT JOIN {0}ORDERS [OR] ON (OD.ORDER_ID = [OR].ORDER_ID)", whLoginID);
                strSelectSql.AppendFormat("  LEFT JOIN {0}SKU S ON (OD.OWNER_ID = S.OWNER_ID) AND (OD.SKU_ID = S.SKU_ID)", whLoginID);
                strSelectSql.AppendFormat("  LEFT JOIN {0}UOM UOM ON (OD.UOM_ID = UOM.UOM_ID)", whLoginID);
                strSelectSql.AppendFormat("   INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = '{0}' ", pubParasInfo.UserID);
                strSelectSql.Append(" WHERE  ( [OD].[STATUS] = 'Shipped' OR [OD].[STATUS] ='PartShipped' )");
            }
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSelectSql.Append(" AND " + sqlWhere[i].ToString());
            }
            if (financeSalesDetailRptQueryEntiry.SHOW_DATE == "N")
            {
                strSelectSql.Append(" GROUP BY OD.OWNER_ID, OD.SKU_ID, OD.UOM_ID, OD.UNIT_PRICE, OD.REMARK,  S.DESCR, S.DESCR2, UOM.UOM_DESCR,S.PACK_ID");
            }
            else
            {
                strSelectSql.Append(" GROUP BY OD.OWNER_ID, OD.SKU_ID, OD.UOM_ID, OD.UNIT_PRICE, OD.REMARK,  S.DESCR, S.DESCR2, UOM.UOM_DESCR, Convert(varchar,[OR].ACTUAL_SHIP_DATE,102),S.PACK_ID");
            }
            string StrSql = strSelectSql.ToString();
            if (!financeSalesDetailRptQueryEntiry.IsGetAll)
            {
                StrSql = PagingHelper.GetPagingSQL(strSelectSql.ToString(), financeSalesDetailRptQueryEntiry.CurrentPage, financeSalesDetailRptQueryEntiry.PageSize, financeSalesDetailRptQueryEntiry.SortField, financeSalesDetailRptQueryEntiry.SortDirection);
            }
            else
            {
                StrSql = StrSql + " ORDER BY " + financeSalesDetailRptQueryEntiry.SortField + " " + financeSalesDetailRptQueryEntiry.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql);
        }

        public DataSet GetGlobalInventoryRptDataSet(List<string> sqlWhere, GlobalInventoryRptQueryEntity globalInventoryRptQueryEntity, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSqlWhere = string.Empty;
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSqlWhere = strSqlWhere + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    strSqlWhere = strSqlWhere + " AND " + sqlWhere[i].ToString();
                }
            }
            StringBuilder strSelectSql = new StringBuilder();
            for (i = 0; whLoginID.Count > i; i++)
            {
                strSelectSql.Append(" SELECT DISTINCT A.SKU_ID,B.DESCR,B.DESCR2,A.OWNER_ID,B.SKU_CLASS_ID,F.DESCR AS SKU_CLASS_DESCR,SUM(A.QTY) AS INVENTORY_QTY,D.PACK_UOM1 AS 'UOM',SUM(CASE C.LOT_ATTR09 WHEN 'OK' THEN A.QTY ELSE 0 END) AS 'OK',SUM(CASE C.LOT_ATTR09 WHEN 'HOLD' THEN A.QTY ELSE 0 END) AS 'HOLD',SUM(CASE C.LOT_ATTR09 WHEN 'DAMAGE' THEN A.QTY ELSE 0 END) AS 'DAMAGE', SUM(CASE A.STATUS WHEN 'HOLD' THEN A.QTY ELSE 0 END) AS 'ONHOLD_QTY',  A.ALLOCATED_QTY,SUM(A.QTY - A.ALLOCATED_QTY - A.PICKED_QTY-CASE A.STATUS WHEN 'HOLD' THEN A.QTY ELSE 0 END) AS AVAILABLE,C.VENDOR_ID ,C.EXTERNAL_LOT,SUM(A.QTY *B.[CUBE] ) AS TOTALCUBE,SUM(A.QTY *B.GROSS_WGT) AS TOTALWGT,MAX(C.RECEIPT_DATE) AS RECEIPT_DATE, MAX(E.UPDATED_DATE) AS SHIPPED_DATE,C.LOT_ATTR01,C.LOT_ATTR02,C.LOT_ATTR03,C.LOT_ATTR04,C.LOT_ATTR05,C.LOT_ATTR06,C.LOT_ATTR07,C.LOT_ATTR08,C.LOT_ATTR09,C.LOT_ATTR10,C.PRODUCE_DATE,C.EXPIRY_DATE ");
                strSelectSql.AppendFormat("  FROM {0}INV_LOT_LOC_LPN A ", whLoginID[i]);
                strSelectSql.AppendFormat("  LEFT JOIN {0}SKU B ON A.SKU_ID =B.SKU_ID AND A.OWNER_ID = B.OWNER_ID", whLoginID[i]);
                strSelectSql.AppendFormat("  LEFT JOIN {0}LOT_ATTRIBUTE C ON A.LOT=C.LOT", whLoginID[i]);
                strSelectSql.AppendFormat("  LEFT JOIN {0}PACK D ON B.PACK_ID= D.PACK_ID", whLoginID[i]);
                strSelectSql.AppendFormat("  LEFT JOIN {0}PICK_DETAIL E ON A.SKU_ID=E.SKU_ID AND B.OWNER_ID = E.OWNER_ID  AND E.STATUS = 'SHIPPED'", whLoginID[i]);
                strSelectSql.AppendFormat("  LEFT JOIN {0}SKU_CLASS F ON B.SKU_CLASS_ID=F.SKU_CLASS_ID AND B.OWNER_ID = F.OWNER_ID", whLoginID[i]);
                strSelectSql.AppendFormat("  INNER JOIN SYSADM.USER_OWNER G ON A.OWNER_ID = G.OWNER_ID AND G.[USER_ID] = '{0}'", pubParasInfo.UserID);
                strSelectSql.AppendFormat("  {0} ", strSqlWhere);
                strSelectSql.AppendFormat("  GROUP BY A.SKU_ID,A.OWNER_ID,B.DESCR,B.DESCR2,B.SKU_CLASS_ID,F.DESCR,C.VENDOR_ID,D.PACK_UOM1,C.EXTERNAL_LOT,A.ALLOCATED_QTY,C.LOT_ATTR01,C.LOT_ATTR02,C.LOT_ATTR03,C.LOT_ATTR04,C.LOT_ATTR05,C.LOT_ATTR06,C.LOT_ATTR07,C.LOT_ATTR08,C.LOT_ATTR09,C.LOT_ATTR10,C.PRODUCE_DATE,C.EXPIRY_DATE", new object[0]);
                if (whLoginID.Count > (i + 1))
                {
                    strSelectSql.AppendFormat(" UNION ALL ", new object[0]);
                }
            }
            strSelectSql.Insert(0, "SELECT SKU_ID,DESCR,DESCR2,OWNER_ID,SKU_CLASS_ID, SKU_CLASS_DESCR,SUM(INVENTORY_QTY) AS INVENTORY_QTY ,UOM,SUM(OK) AS OK,SUM(HOLD) AS HOLD,SUM(DAMAGE) AS DAMAGE,SUM(ONHOLD_QTY) AS ONHOLD_QTY,SUM(ALLOCATED_QTY) AS ALLOCATED_QTY,SUM(AVAILABLE) AS AVAILABLE,SUM(TOTALCUBE) AS TOTALCUBE,SUM(TOTALWGT) AS TOTALWGT,max(RECEIPT_DATE) AS RECEIPT_DATE,Max(SHIPPED_DATE) AS SHIPPED_DATE,VENDOR_ID,EXTERNAL_LOT,LOT_ATTR01,LOT_ATTR02,LOT_ATTR03,LOT_ATTR04,LOT_ATTR05,LOT_ATTR06,LOT_ATTR07,LOT_ATTR08,LOT_ATTR09,LOT_ATTR10,PRODUCE_DATE,EXPIRY_DATE  FROM(  ");
            strSelectSql.Append(" ) INV WHERE  INVENTORY_QTY>0 GROUP BY SKU_ID,OWNER_ID,DESCR,DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR,VENDOR_ID,UOM,EXTERNAL_LOT,ALLOCATED_QTY,VENDOR_ID,EXTERNAL_LOT,LOT_ATTR01,LOT_ATTR02,LOT_ATTR03,LOT_ATTR04,LOT_ATTR05,LOT_ATTR06,LOT_ATTR07,LOT_ATTR08,LOT_ATTR09,LOT_ATTR10,PRODUCE_DATE,EXPIRY_DATE");
            string StrSql = strSelectSql.ToString();
            if (!globalInventoryRptQueryEntity.IsGetAll)
            {
                StrSql = PagingHelper.GetPagingSQL(strSelectSql.ToString(), globalInventoryRptQueryEntity.CurrentPage, globalInventoryRptQueryEntity.PageSize, globalInventoryRptQueryEntity.SortField, globalInventoryRptQueryEntity.SortDirection);
            }
            else
            {
                StrSql = StrSql + " ORDER BY " + globalInventoryRptQueryEntity.SortField + " " + globalInventoryRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql);
        }

        public DataSet GetIdleInventoryRptDataSet(List<string> sqlWhere, IdleInventoryRptQueryEntiry idleInventoryRptQueryEntiry, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT * FROM ( ");
            sbSql.Append(" SELECT ILLL.OWNER_ID, ILLL.SKU_ID, ILLL.LOC, LA.RECEIPT_DATE, LA.LOT_ATTR08, SUM(ILLL.QTY) AS QTY, DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) AS DAYS ");
            sbSql.Append(" FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = ILLL.OWNER_ID AND LA.SKU_ID = ILLL.SKU_ID AND LA.LOT = ILLL.LOT ");
            sbSql.Append(" INNER JOIN SYSADM.USER_OWNER UO ON ILLL.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            sbSql.Append(" WHERE ILLL.QTY > 0 ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sbSql.Append(" AND " + sqlWhere[i].ToString());
            }
            if (idleInventoryRptQueryEntiry.MIN_DAYS > 0)
            {
                sbSql.Append(" AND DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) >= " + idleInventoryRptQueryEntiry.MIN_DAYS.ToString());
            }
            if (idleInventoryRptQueryEntiry.MAX_DAYS > 0)
            {
                sbSql.Append(" AND DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) <= " + idleInventoryRptQueryEntiry.MAX_DAYS.ToString());
            }
            sbSql.Append(" GROUP BY ILLL.OWNER_ID, ILLL.SKU_ID, ILLL.LOC, LA.RECEIPT_DATE, LA.LOT_ATTR08 ) tmp");
            string sql = sbSql.ToString().Replace("@_@", whLoginID);
            if (!idleInventoryRptQueryEntiry.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, idleInventoryRptQueryEntiry.CurrentPage, idleInventoryRptQueryEntiry.PageSize, idleInventoryRptQueryEntiry.SortField, idleInventoryRptQueryEntiry.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + idleInventoryRptQueryEntiry.SortField + " " + idleInventoryRptQueryEntiry.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetInOutBlanaceRptDataSet(List<string> sqlWhere, InOutBlanaceRptQueryEntity inOutBlanaceRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strOwner;
            string sql = string.Empty;
            string strWhere = string.Empty;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strWhere = strWhere + " AND " + sqlWhere[i].ToString();
            }
            if (inOutBlanaceRptQueryEntity.ShowLot == "Y")
            {
                strOwner = string.Empty;
                if (!string.IsNullOrEmpty(inOutBlanaceRptQueryEntity.OwnerID))
                {
                    strOwner = " and IT.OWNER_ID" + inOutBlanaceRptQueryEntity.OwnerID;
                }
                sql = " SELECT il.OWNER_ID, il.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR, la.VENDOR_ID,s.udf2,s.SKU_GROUP,SC.DESCR AS SKU_CLASS_DESCR,   ISNULL(A.UNIT_PRICE,0) AS UNIT_PRICE,ISNULL(A.UNIT_PRICE,0) AS INIT_PRICE,ISNULL(A.UNIT_PRICE,0)*ISNULL( SUM(iL.BEGINQTY),0) AS INIT_AMOUNT,  ISNULL(SUM(iL.INQTY),0)*ISNULL(A.UNIT_PRICE,0) AS IN_AMOUNT,ISNULL(SUM(iL.OTQTY),0)*ISNULL(A.UNIT_PRICE,0) AS OUT_AMOUNT,  ISNULL(SUM(iL.IN_R_QTY),0)*ISNULL(A.UNIT_PRICE,0) AS IN_R_AMOUNT, ISNULL(SUM(iL.OT_R_QTY),0)*ISNULL(A.UNIT_PRICE,0) AS OUT_R_AMOUNT,  SUM(iL.BEGINQTY) AS INIT_QTY, SUM(iL.INQTY) AS IN_QTY, SUM(iL.IN_R_QTY) AS IN_R_QTY, SUM(iL.OTQTY) AS OUT_QTY, SUM(iL.OT_R_QTY) AS OUT_R_QTY, SUM(iL.AJQTY) AS AJ_QTY,   ISNULL(SUM(iL.AJQTY),0)*ISNULL(A.UNIT_PRICE,0) AS AJ_AMOUNT,ISNULL(A.UNIT_PRICE,0) AS END_PRICE,  ISNULL(A.UNIT_PRICE,0)* ISNULL(SUM(iL.ENDQTY),0) AS END_AMOUNT,  ISNULL(SUM(iL.ENDQTY),0) AS END_QTY,  ISNULL(SUM(iL.ENDQTY),0) * s.[CUBE] AS TOTALCUBE,   ISNULL(SUM(iL.ENDQTY),0) * s.GROSS_WGT AS TOTALWGT,  SUM(A.QTY-A.PREALLOCATED_QTY-ALLOCATED_QTY-PICKED_QTY-ONHOLD_QTY) AS END_AVAILABLE_QTY,  LA.EXTERNAL_LOT AS EXTERNAL_LOT  ,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.RECEIPT_DATE AS RECEIPT_DATE, LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03,  LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05, LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08, LA.LOT_ATTR09 AS LOT_ATTR09,  LA.LOT_ATTR10 AS LOT_ATTR10,s.PACK_ID,u.UOM_DESCR   FROM (  SELECT IT.LOT, IT.OWNER_ID, IT.SKU_ID,  SUM(CASE WHEN IT.TRANS_DATE < @BEGIN_DATE AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')  THEN IT.QTY ELSE 0 END) AS BEGINQTY,  SUM(CASE WHEN IT.TRANS_DATE <= @END_DATE AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')   THEN IT.QTY ELSE 0 END) AS ENDQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' AND ISNULL(R.RECEIPT_TYPE,'') <> 'RETURN' THEN IT.QTY ELSE 0 END) AS INQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' AND ISNULL(R.RECEIPT_TYPE,'') = 'RETURN' THEN IT.QTY ELSE 0 END) AS IN_R_QTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE IN ('AJADJUSTMENTDETAILADD','AJCYCLECOUNTADD','MVTRANSFERDETAILADD','MVTRANSFERDETAILUPDATE')  THEN IT.QTY ELSE 0 END) AS AJQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' AND ISNULL(O.TYPE,'') <> 'RETURN'  THEN IT.QTY ELSE 0 END) AS OTQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' AND ISNULL(O.TYPE,'') = 'RETURN'  THEN IT.QTY ELSE 0 END) AS OT_R_QTY  FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT R ON IT.DOC_ID = R.RECEIPT_ID AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK'   LEFT JOIN @_@PICK_DETAIL PD ON PD.PICK_DETAIL_ID=IT.DOC_ID AND IT.TRANS_TYPE = 'OT' LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID where  1=1 " + strOwner + " GROUP BY IT.LOT, IT.OWNER_ID, IT.SKU_ID) IL  LEFT JOIN @_@INV_LOT A ON A.LOT = iL.LOT AND A.OWNER_ID = iL.OWNER_ID AND A.SKU_ID = iL.SKU_ID  LEFT JOIN @_@SKU s ON s.OWNER_ID = iL.OWNER_ID AND s.SKU_ID = iL.SKU_ID  LEFT JOIN @_@PACK p ON p.PACK_ID = s.PACK_ID  LEFT JOIN @_@UOM u ON u.UOM_ID = p.PACK_UOM1  LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = iL.LOT AND la.OWNER_ID = iL.OWNER_ID AND la.SKU_ID = iL.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON s.OWNER_ID = SC.OWNER_ID AND s.SKU_CLASS_ID = SC.SKU_CLASS_ID  INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = iL.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE ( ISNULL(iL.BEGINQTY,0) <> 0 OR ISNULL(iL.INQTY,0) <> 0 OR ISNULL(iL.IN_R_QTY,0) <> 0 OR ISNULL(iL.OTQTY,0) <> 0 OR ISNULL(iL.ENDQTY,0) <> 0  OR  ISNULL(iL.OT_R_QTY,0) <> 0  OR  ISNULL(iL.AJQTY,0) <> 0  ) " + strWhere + " GROUP BY il.OWNER_ID, il.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR, la.VENDOR_ID,s.udf2,s.SKU_GROUP,SC.DESCR ,A.unit_price,  LA.EXTERNAL_LOT ,LA.PRODUCE_DATE ,LA.EXPIRY_DATE ,LA.RECEIPT_DATE , LA.LOT_ATTR01 ,LA.LOT_ATTR02 ,LA.LOT_ATTR03 ,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06 ,LA.LOT_ATTR07 ,LA.LOT_ATTR08 , LA.LOT_ATTR09 ,LA.LOT_ATTR10,s.PACK_ID,u.UOM_DESCR  ";
            }
            else
            {
                strOwner = string.Empty;
                if (!string.IsNullOrEmpty(inOutBlanaceRptQueryEntity.OwnerID))
                {
                    strOwner = " and IT.OWNER_ID" + inOutBlanaceRptQueryEntity.OwnerID;
                }
                sql = " SELECT il.OWNER_ID, il.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR, s.udf2,s.SKU_GROUP,SC.DESCR AS SKU_CLASS_DESCR,   ISNULL(A.UNIT_PRICE,0) AS UNIT_PRICE,ISNULL(A.UNIT_PRICE,0) AS INIT_PRICE,ISNULL(A.UNIT_PRICE,0)*ISNULL( SUM(iL.BEGINQTY),0) AS INIT_AMOUNT,  ISNULL(SUM(iL.INQTY),0)*ISNULL(A.UNIT_PRICE,0) AS IN_AMOUNT,ISNULL(SUM(iL.OTQTY),0)*ISNULL(A.UNIT_PRICE,0) AS OUT_AMOUNT,  ISNULL(SUM(iL.IN_R_QTY),0)*ISNULL(A.UNIT_PRICE,0) AS IN_R_AMOUNT, ISNULL(SUM(iL.OT_R_QTY),0)*ISNULL(A.UNIT_PRICE,0) AS OUT_R_AMOUNT,  SUM(iL.BEGINQTY) AS INIT_QTY, SUM(iL.INQTY) AS IN_QTY, SUM(iL.IN_R_QTY) AS IN_R_QTY, SUM(iL.OTQTY) AS OUT_QTY, SUM(iL.OT_R_QTY) AS OUT_R_QTY, SUM(iL.AJQTY) AS AJ_QTY,   ISNULL(SUM(iL.AJQTY),0)*ISNULL(A.UNIT_PRICE,0) AS AJ_AMOUNT,ISNULL(A.UNIT_PRICE,0) AS END_PRICE,  ISNULL(A.UNIT_PRICE,0)* ISNULL(SUM(iL.ENDQTY),0) AS END_AMOUNT,  ISNULL(SUM(iL.ENDQTY),0) AS END_QTY,  ISNULL(SUM(iL.ENDQTY),0) * s.[CUBE] AS TOTALCUBE,   ISNULL(SUM(iL.ENDQTY),0) * s.GROSS_WGT AS TOTALWGT,  SUM(A.QTY-A.PREALLOCATED_QTY-ALLOCATED_QTY-PICKED_QTY-ONHOLD_QTY) AS END_AVAILABLE_QTY ,s.PACK_ID,u.UOM_DESCR FROM (  SELECT IT.LOT, IT.OWNER_ID, IT.SKU_ID,  SUM(CASE WHEN IT.TRANS_DATE < @BEGIN_DATE AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')  THEN IT.QTY ELSE 0 END) AS BEGINQTY,  SUM(CASE WHEN IT.TRANS_DATE <= @END_DATE AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')   THEN IT.QTY ELSE 0 END) AS ENDQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' AND ISNULL(R.RECEIPT_TYPE,'') <> 'RETURN' THEN IT.QTY ELSE 0 END) AS INQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' AND ISNULL(R.RECEIPT_TYPE,'') = 'RETURN' THEN IT.QTY ELSE 0 END) AS IN_R_QTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE  IN ('AJADJUSTMENTDETAILADD','AJCYCLECOUNTADD','MVTRANSFERDETAILADD','MVTRANSFERDETAILUPDATE')  THEN IT.QTY ELSE 0 END) AS AJQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' AND ISNULL(O.TYPE,'') <> 'RETURN'  THEN IT.QTY ELSE 0 END) AS OTQTY,  SUM(CASE WHEN IT.TRANS_DATE >= @BEGIN_DATE AND IT.TRANS_DATE <= @END_DATE  AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' AND ISNULL(O.TYPE,'') = 'RETURN'  THEN IT.QTY ELSE 0 END) AS OT_R_QTY  FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT R ON IT.DOC_ID = R.RECEIPT_ID AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK'  LEFT JOIN @_@PICK_DETAIL PD ON PD.PICK_DETAIL_ID=IT.DOC_ID AND IT.TRANS_TYPE = 'OT' LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID where  1=1 " + strOwner + " GROUP BY IT.LOT, IT.OWNER_ID, IT.SKU_ID) IL  LEFT JOIN @_@INV_LOT A ON A.LOT = iL.LOT AND A.OWNER_ID = iL.OWNER_ID AND A.SKU_ID = iL.SKU_ID  LEFT JOIN @_@SKU s ON s.OWNER_ID = iL.OWNER_ID AND s.SKU_ID = iL.SKU_ID  LEFT JOIN @_@PACK p ON p.PACK_ID = s.PACK_ID  LEFT JOIN @_@UOM u ON u.UOM_ID = p.PACK_UOM1  LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = iL.LOT AND la.OWNER_ID = iL.OWNER_ID AND la.SKU_ID = iL.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON s.OWNER_ID = SC.OWNER_ID AND s.SKU_CLASS_ID = SC.SKU_CLASS_ID  INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = iL.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE (  ISNULL(iL.BEGINQTY,0) <> 0 OR ISNULL(iL.INQTY,0) <> 0 OR ISNULL(iL.IN_R_QTY,0) <> 0 OR ISNULL(iL.OTQTY,0) <> 0 OR ISNULL(iL.ENDQTY,0) <> 0  OR  ISNULL(iL.OT_R_QTY,0) <> 0  OR  ISNULL(iL.AJQTY,0) <> 0 ) " + strWhere + " GROUP BY il.OWNER_ID, il.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR, s.udf2,s.SKU_GROUP,SC.DESCR ,A.unit_price,s.PACK_ID,u.UOM_DESCR ";
            }
            sql = sql.Replace("@BEGIN_DATE", "'" + inOutBlanaceRptQueryEntity.StartTime + "'").Replace("@END_DATE", "'" + inOutBlanaceRptQueryEntity.EndTime + "'").Replace("@_@", whLoginID).ToString();
            if (!inOutBlanaceRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, inOutBlanaceRptQueryEntity.CurrentPage, inOutBlanaceRptQueryEntity.PageSize, inOutBlanaceRptQueryEntity.SortField, inOutBlanaceRptQueryEntity.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + inOutBlanaceRptQueryEntity.SortField + " " + inOutBlanaceRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetInOutStockRptDetailInfo(List<string> sqlWhere, InOutStockRptDetailQueryEntity inOutStockRptDetailQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT DISTINCT * FROM( SELECT A.SKU_ID,B.DESCR,A.TRANS_DATE AS TransactionDate ,T.[TEXT] AS TransactionType, A.OWNER_ID,B.SKU_CLASS_ID,CASE TRANS_TYPE  WHEN 'IN' THEN  C.EXTERNAL_RECEIPT_ID  WHEN 'OT' THEN D.EXTERN_ORDER_ID  ELSE '' END AS ExternalOrderID, A.DOC_ID AS OrderID, A.QTY, @_@GET_TRANS_INV(A.OWNER_ID, A.SKU_ID, A.LOT, A.TRANS_DATE) AS EndInginventory ,A.CREATED_BY AS CraateBy,");
            strSql.Append(" A.LOT_ATTR01,A.LOT_ATTR02,A.LOT_ATTR03,A.LOT_ATTR04,A.LOT_ATTR05,A.LOT_ATTR06,A.LOT_ATTR07,A.LOT_ATTR08, A.LOT_ATTR09,A.LOT_ATTR10,A.PRODUCE_DATE,A.EXPIRY_DATE,A.EXTERNAL_LOT,A.VENDOR_ID,C.RECEIPT_ID");
            strSql.Append(" FROM @_@INV_TRANS A INNER JOIN @_@SKU B  ON A.OWNER_ID=B.OWNER_ID AND A.SKU_ID=B.SKU_ID ");
            strSql.Append(" LEFT JOIN SYSADM.TRANSLATION T ON 'TRANSTYPE.'+A.TRANS_TYPE = T.ITEM_ID  ");
            strSql.Append(" AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE");
            strSql.Append(" LEFT JOIN @_@RECEIPT  C ON A.DOC_ID= C.RECEIPT_ID ");
            strSql.Append(" LEFT JOIN @_@ORDERS   D ON A.DOC_ID= D.ORDER_ID ");
            strSql.Append(" WHERE A.VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE [USER_ID] = @USER_ID) OR A.VENDOR_ID = '' OR A.VENDOR_ID IS NULL) TEMP ");
            strSql.Append(" INNER JOIN SYSADM.USER_OWNER UO ON TEMP.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID");
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!inOutStockRptDetailQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, inOutStockRptDetailQueryEntity.CurrentPage, inOutStockRptDetailQueryEntity.PageSize, inOutStockRptDetailQueryEntity.SortField, inOutStockRptDetailQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + inOutStockRptDetailQueryEntity.SortField + " " + inOutStockRptDetailQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetInOutStockRptInfo(List<string> sqlWhere, InOutStockRptQueryEntity InOutStockRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            StringBuilder strSql = new StringBuilder();
            if (InOutStockRptQuery.IsLotAttri == "1")
            {
                strSql.Append(" SELECT * FROM ( ");
                strSql.Append(" SELECT A.OWNER_ID, A.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR,s.PACK_ID, la.VENDOR_ID, ");
                strSql.Append(" il.UNIT_PRICE, la.EXTERNAL_LOT, la.PRODUCE_DATE, la.[EXPIRY_DATE], la.RECEIPT_DATE, la.LOT, ");
                strSql.Append(" la.LOT_ATTR01, la.LOT_ATTR02, la.LOT_ATTR03, la.LOT_ATTR04, la.LOT_ATTR05, ");
                strSql.Append(" la.LOT_ATTR06, la.LOT_ATTR07, la.LOT_ATTR08, la.LOT_ATTR09, la.LOT_ATTR10, ");
                strSql.Append("   SUM(A.BEGINQTY) AS BEGINQTY, SUM(A.INQTY) AS INQTY, SUM(A.OTQTY) AS OTQTY, SUM(A.AJQTY) AS AJQTY,  ");
                strSql.Append("   ISNULL(SUM(A.ENDQTY),0) AS QTY,ISNULL(SUM(A.ENDQTY),0) * s.[CUBE] AS TOTALCUBE, ISNULL(SUM(A.ENDQTY),0) * s.GROSS_WGT AS TOTALWGT ");
                strSql.Append(" FROM ( ");
                strSql.Append(" SELECT IT.LOT, IT.OWNER_ID, IT.SKU_ID, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE < @begin AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')  THEN IT.QTY ELSE 0 END) AS BEGINQTY , ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE <= @end AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')   THEN IT.QTY ELSE 0 END) AS ENDQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' THEN IT.QTY ELSE 0 END) AS INQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE IN ('AJADJUSTMENTDETAILADD','AJCYCLECOUNTADD','MVTRANSFERDETAILADD','MVTRANSFERDETAILUPDATE')  THEN IT.QTY ELSE 0 END) AS AJQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' THEN IT.QTY ELSE 0 END) AS OTQTY ");
                strSql.Append(" FROM @_@INV_TRANS IT ");
                strSql.Append(" GROUP BY IT.LOT, IT.OWNER_ID, IT.SKU_ID) A ");
                strSql.Append(" LEFT JOIN @_@INV_LOT il ON il.LOT = A.LOT ");
                strSql.Append(" LEFT JOIN @_@SKU s ON s.OWNER_ID = A.OWNER_ID AND s.SKU_ID = A.SKU_ID ");
                strSql.Append(" LEFT JOIN @_@PACK p ON p.PACK_ID = s.PACK_ID ");
                strSql.Append(" LEFT JOIN @_@UOM u ON u.UOM_ID = p.PACK_UOM1 ");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = A.LOT ");
                strSql.AppendFormat(" INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = A.OWNER_ID AND UO.[USER_ID] = '{0}' ", pubParasInfo.UserID);
                strSql.Append(" WHERE (ISNULL(A.BEGINQTY,0) <> 0 OR ISNULL(A.INQTY,0) <> 0 OR ISNULL(A.AJQTY,0) <> 0 OR ISNULL(A.OTQTY,0) <> 0 OR ISNULL(A.ENDQTY,0) <> 0) ");
                strSql.AppendFormat(" AND (LA.VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE USER_ID = '{0}') OR LA.VENDOR_ID = '' OR LA.VENDOR_ID IS NULL) ", pubParasInfo.UserID);
                for (i = 0; i < sqlWhere.Count; i++)
                {
                    strSql.Append(" AND " + sqlWhere[i].ToString());
                }
                strSql.Append(" GROUP BY A.OWNER_ID, A.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR,s.PACK_ID, la.VENDOR_ID, ");
                strSql.Append(" il.UNIT_PRICE, la.EXTERNAL_LOT, la.PRODUCE_DATE, la.[EXPIRY_DATE], la.RECEIPT_DATE, la.LOT, ");
                strSql.Append(" la.LOT_ATTR01, la.LOT_ATTR02, la.LOT_ATTR03, la.LOT_ATTR04, la.LOT_ATTR05, ");
                strSql.Append(" la.LOT_ATTR06, la.LOT_ATTR07, la.LOT_ATTR08, la.LOT_ATTR09, la.LOT_ATTR10 ");
                strSql.Append(" ) TEMP ");
            }
            else
            {
                strSql.Append(" SELECT * FROM ( ");
                strSql.Append(" SELECT A.OWNER_ID, A.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR,s.PACK_ID, la.VENDOR_ID, ");
                strSql.Append("   SUM(A.BEGINQTY) AS BEGINQTY, SUM(A.INQTY) AS INQTY, SUM(A.OTQTY) AS OTQTY, SUM(A.AJQTY) AS AJQTY,  ");
                strSql.Append("   ISNULL(SUM(A.ENDQTY),0) AS QTY,ISNULL(SUM(A.ENDQTY),0) * s.[CUBE] AS TOTALCUBE, ISNULL(SUM(A.ENDQTY),0) * s.GROSS_WGT AS TOTALWGT ");
                strSql.Append(" FROM ( ");
                strSql.Append(" SELECT IT.LOT, IT.OWNER_ID, IT.SKU_ID, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE < @begin AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')  THEN IT.QTY ELSE 0 END) AS BEGINQTY  , ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE <= @end AND IT.SOURCE_TRANS_TYPE NOT IN ('Picking','PutawayMove','ConsoleMove')  THEN IT.QTY ELSE 0 END) AS ENDQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE + IT.[STATUS] = 'INRECEIPTDETAILADDOK' THEN IT.QTY ELSE 0 END) AS INQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE + IT.SOURCE_TRANS_TYPE   IN ('AJADJUSTMENTDETAILADD','AJCYCLECOUNTADD','MVTRANSFERDETAILADD','MVTRANSFERDETAILUPDATE')  THEN IT.QTY ELSE 0 END) AS AJQTY, ");
                strSql.Append("   SUM(CASE WHEN IT.TRANS_DATE >= @begin AND IT.TRANS_DATE <= @end ");
                strSql.Append("       AND IT.TRANS_TYPE+IT.STATUS = 'OTOK' THEN IT.QTY ELSE 0 END) AS OTQTY ");
                strSql.Append(" FROM @_@INV_TRANS IT ");
                strSql.Append(" GROUP BY IT.LOT, IT.OWNER_ID, IT.SKU_ID) A ");
                strSql.Append(" LEFT JOIN @_@INV_LOT il ON il.LOT = A.LOT ");
                strSql.Append(" LEFT JOIN @_@SKU s ON s.OWNER_ID = A.OWNER_ID AND s.SKU_ID = A.SKU_ID ");
                strSql.Append(" LEFT JOIN @_@PACK p ON p.PACK_ID = s.PACK_ID ");
                strSql.Append(" LEFT JOIN @_@UOM u ON u.UOM_ID = p.PACK_UOM1 ");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE la ON la.LOT = A.LOT ");
                strSql.AppendFormat(" INNER JOIN SYSADM.USER_OWNER UO ON UO.OWNER_ID = A.OWNER_ID AND UO.[USER_ID] = '{0}' ", pubParasInfo.UserID);
                strSql.Append(" WHERE (ISNULL(A.BEGINQTY,0) <> 0 OR ISNULL(A.INQTY,0) <> 0 OR ISNULL(A.AJQTY,0) <> 0 OR ISNULL(A.OTQTY,0) <> 0 OR ISNULL(A.ENDQTY,0) <> 0) ");
                strSql.AppendFormat(" AND (LA.VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE USER_ID = '{0}') OR LA.VENDOR_ID = '' OR LA.VENDOR_ID IS NULL) ", pubParasInfo.UserID);
                for (i = 0; i < sqlWhere.Count; i++)
                {
                    strSql.Append(" AND " + sqlWhere[i].ToString());
                }
                strSql.Append(" GROUP BY A.OWNER_ID, A.SKU_ID, s.DESCR, s.DESCR2, s.SKU_CLASS_ID, s.[CUBE], s.GROSS_WGT, u.UOM_DESCR, la.VENDOR_ID,s.PACK_ID ");
                strSql.Append(" ) TEMP ");
            }
            string sql = string.Empty;
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@begin", "'" + InOutStockRptQuery.STARTDATETIME + "'").Replace("@end", "'" + InOutStockRptQuery.ENDDATETIME + "'");
            if (!InOutStockRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, InOutStockRptQuery.CurrentPage, InOutStockRptQuery.PageSize, InOutStockRptQuery.SortField, InOutStockRptQuery.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + InOutStockRptQuery.SortField + " " + InOutStockRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetInvLocSkyTransRptDataSet(List<string> sqlWhere, InvLocSkyTransRptQueryEntity invLocSkyTransRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append(" SELECT OWNER_ID, SUM(CASE WHEN CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @BEGIN_DATE THEN ISNULL(INV_LOC,0) END) AS BEGIN_LOC, ");
            sbSQL.Append("  SUM(ISNULL(IN_LOC,0)) AS IN_LOC, SUM(ISNULL(OUT_LOC,0)) AS OUT_LOC, ");
            sbSQL.Append("  SUM(CASE WHEN CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) = @END_DATE THEN ISNULL(INV_LOC,0) END) AS END_LOC");
            sbSQL.Append(" FROM @_@DAILY_INV_LOC_SKYTRANS");
            sbSQL.Append(" WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), INV_DATE, 20)) BETWEEN @BEGIN_DATE AND @END_DATE ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sbSQL.Append(" AND " + sqlWhere[i].ToString());
            }
            sbSQL.Append(" GROUP BY OWNER_ID ");
            string sql = sbSQL.ToString().Replace("@_@", whLoginID).Replace("@BEGIN_DATE", "'" + invLocSkyTransRptQueryEntity.BEGIN_DATE.ToString("yyyy-MM-dd") + "'").Replace("@END_DATE", "'" + invLocSkyTransRptQueryEntity.END_DATE.ToString("yyyy-MM-dd") + "'");
            if (!invLocSkyTransRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, invLocSkyTransRptQueryEntity.CurrentPage, invLocSkyTransRptQueryEntity.PageSize, invLocSkyTransRptQueryEntity.SortField, invLocSkyTransRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + invLocSkyTransRptQueryEntity.SortField + " " + invLocSkyTransRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<LocationRptInfo> GetLocationRptInfo(List<string> sqlWhere, LocationRptQueryEntity locationRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSql = "SELECT DISTINCT LOC.LOC AS LOC,LOC.CUBIC_CAPACITY AS CUBIC_CAPACITY,TEMP.ONHAND_CUBE AS CUBE_ONHAND,LOC.ZONE_ID AS ZONE_ID,  LOC.LOC_USAGE AS LOC_USAGE,  T.[TEXT] AS T_LOC_USAGE,  LOC.ABC AS ABC,  T3.[TEXT] AS T_ABC,  LOC.LOGICAL_LOC AS LOGICAL_LOC,  LOC.LOC_FLAG AS LOC_FLAG,  T1.[TEXT] AS T_LOC_FLAG,  LOC.IS_MULTI_SKU AS IS_MULTI_SKU,LOC.IS_MULTI_LOT AS IS_MULTI_LOT,LOC.CHECK_DIG AS CHECK_DIG, CASE LOC.CUBIC_CAPACITY WHEN 0 THEN '0' ELSE CAST((TEMP.ONHAND_CUBE / LOC.CUBIC_CAPACITY * 100) AS VARCHAR) END + '%' AS CUBE_ONHAND_PERCENTAGE, LOC.CUBIC_CAPACITY-TEMP.ONHAND_CUBE AS AVAILABLE_CUBE,  LOC.STATUS AS STATUS ,  T2.[TEXT] AS T_STATUS  FROM @_@LOCATION LOC  LEFT JOIN @_@INV_SKU_LOC ISL ON LOC.LOC = ISL.LOC  LEFT JOIN @_@SKU SKU ON ISL.OWNER_ID = SKU.OWNER_ID AND ISL.SKU_ID = SKU.SKU_ID LEFT JOIN (SELECT ISL.LOC,SUM(ISL.QTY * SKU.[CUBE]) AS ONHAND_CUBE FROM @_@INV_SKU_LOC ISL LEFT JOIN @_@SKU SKU ON ISL.OWNER_ID = SKU.OWNER_ID AND ISL.SKU_ID = SKU.SKU_ID GROUP BY ISL.LOC)   TEMP ON ISL.LOC = TEMP.LOC  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = LOC.LOC_USAGE AND SCD.SYS_CODE_TYPE = 'LocUsage'  LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = LOC.LOC_FLAG AND SCD2.SYS_CODE_TYPE = 'LocFlag'  LEFT JOIN @_@SYS_CODE_DETAIL SCD3 ON SCD3.SYS_CODE = LOC.STATUS AND SCD3.SYS_CODE_TYPE = 'LocFlag'  LEFT JOIN @_@SYS_CODE_DETAIL SCD4 ON SCD4.SYS_CODE = LOC.ABC AND SCD3.SYS_CODE_TYPE = 'ABC'  LEFT JOIN sysadm.TRANSLATION T ON 'LocUsage.'+LOC.LOC_USAGE = T.ITEM_ID      AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T1 ON 'LocFlag.'+LOC.LOC_FLAG = T1.ITEM_ID      AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T2 ON 'LocFlag.'+LOC.STATUS = T2.ITEM_ID     AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T3 ON 'ABC.'+LOC.ABC = T3.ITEM_ID      AND  T3.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T3.LANG+'-'+T3.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON ISL.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!locationRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, locationRptQuery.CurrentPage, locationRptQuery.PageSize, locationRptQuery.SortField, locationRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + locationRptQuery.SortField + " " + locationRptQuery.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<LocationRptInfo> locationRptInfos = new List<LocationRptInfo>();
            LocationRptInfo locationRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            locationRptInfo = new LocationRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["ZONE_ID"].ToString(), ds.Tables[2].Rows[i]["LOC_USAGE"].ToString(), ds.Tables[2].Rows[i]["ABC"].ToString(), ds.Tables[2].Rows[i]["STATUS"].ToString(), ds.Tables[2].Rows[i]["LOGICAL_LOC"].ToString(), ds.Tables[2].Rows[i]["LOC_FLAG"].ToString(), ds.Tables[2].Rows[i]["IS_MULTI_SKU"].ToString(), ds.Tables[2].Rows[i]["IS_MULTI_LOT"].ToString(), ds.Tables[2].Rows[i]["CHECK_DIG"].ToString(), (ds.Tables[2].Rows[i]["CUBIC_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[2].Rows[i]["CUBIC_CAPACITY"].ToString()) : 0f, ds.Tables[2].Rows[i]["CUBE_ONHAND_PERCENTAGE"].ToString(), (ds.Tables[2].Rows[i]["CUBE_ONHAND"].ToString() != "") ? float.Parse(ds.Tables[2].Rows[i]["CUBE_ONHAND"].ToString()) : 0f, ds.Tables[2].Rows[i]["T_LOC_USAGE"].ToString(), ds.Tables[2].Rows[i]["T_ABC"].ToString(), ds.Tables[2].Rows[i]["T_STATUS"].ToString(), ds.Tables[2].Rows[i]["T_LOC_FLAG"].ToString());
                            locationRptInfos.Add(locationRptInfo);
                        }
                    }
                    if ((locationRptInfos != null) && (locationRptInfos.Count > 0))
                    {
                        locationRptInfos[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        locationRptInfos[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        locationRptInfos[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return locationRptInfos;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return locationRptInfos;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    locationRptInfo = new LocationRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["ZONE_ID"].ToString(), ds.Tables[0].Rows[i]["LOC_USAGE"].ToString(), ds.Tables[0].Rows[i]["ABC"].ToString(), ds.Tables[0].Rows[i]["STATUS"].ToString(), ds.Tables[0].Rows[i]["LOGICAL_LOC"].ToString(), ds.Tables[0].Rows[i]["LOC_FLAG"].ToString(), ds.Tables[0].Rows[i]["IS_MULTI_SKU"].ToString(), ds.Tables[0].Rows[i]["IS_MULTI_LOT"].ToString(), ds.Tables[0].Rows[i]["CHECK_DIG"].ToString(), (ds.Tables[0].Rows[i]["CUBIC_CAPACITY"].ToString() != "") ? float.Parse(ds.Tables[0].Rows[i]["CUBIC_CAPACITY"].ToString()) : 0f, ds.Tables[0].Rows[i]["CUBE_ONHAND_PERCENTAGE"].ToString(), (ds.Tables[0].Rows[i]["CUBE_ONHAND"].ToString() != "") ? float.Parse(ds.Tables[0].Rows[i]["CUBE_ONHAND"].ToString()) : 0f, ds.Tables[0].Rows[i]["T_LOC_USAGE"].ToString(), ds.Tables[0].Rows[i]["T_ABC"].ToString(), ds.Tables[0].Rows[i]["T_STATUS"].ToString(), ds.Tables[0].Rows[i]["T_LOC_FLAG"].ToString());
                    locationRptInfos.Add(locationRptInfo);
                }
                locationRptInfos[0].CURRENTPAGE = "0";
                locationRptInfos[0].TOTALPAGESCOUNT = "0";
                locationRptInfos[0].TOTALRECORDSCOUNT = "0";
            }
            return locationRptInfos;
        }

        public DataSet GetOnTheWayOrderRptDataSet(List<string> sqlWhere, OnTheWayOrderRptQueryEntity onTheWayOrderRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT A.PO_ID AS PO_ID,A.PO_DATE AS PO_DATE,A.REQUEST_DELIVERY_DATE AS REQUEST_DELIVERY_DATE,A.VENDOR_ID AS VENDOR_ID,A.OTHER_REF AS OTHER_REF,A.EXTERNAL_PO_ID AS EXTERNAL_PO_ID, T2.[TEXT] AS PO_TYPE,T.[TEXT] AS STATUS,A.OWNER_ID AS OWNER_ID,B.SKU_ID AS SKU_ID,B.SKU_DESC AS SKU_DESC, ");
            strSql.Append("  B.ORDERED_QTY-RECEIVED_QTY AS QTY,B.PACK_ID AS PACK_ID,B.UOM_ID AS UOM_ID,D.UOM_DESCR AS UOM_DESCR,B.UNIT_PRICE AS UNIT_PRICE,B.TOTAL_PRICE AS TOTAL_PRICE, B.TOTAL_CUBE AS TOTAL_CUBE,B.TOTAL_GROSS_WEIGHT AS TOTAL_GROSS_WEIGHT,B.REMARK AS REMARK,C.SKU_CLASS_ID AS SKU_CLASS_ID,C.SKU_GROUP AS SKU_GROUP,C.DESCR2 AS SKU_DESCR2, C.UDF2 AS SKU_UDF2,A.CREATED_BY AS CREATED_BY,A.CREATED_DATE AS CREATED_DATE,A.UPDATED_BY AS UPDATED_BY,A.UPDATED_DATE AS UPDATED_DATE ");
            strSql.Append("  FROM @_@PURCHASE_ORDER A  INNER JOIN  @_@PURCHASE_ORDER_DETAIL B ON A.PO_ID =B.PO_ID  INNER JOIN @_@SKU C ON B.OWNER_ID =C.OWNER_ID AND B.SKU_ID = C.SKU_ID  INNER JOIN @_@UOM D ON B.UOM_ID=D.UOM_ID");
            strSql.Append("  LEFT JOIN sysadm.TRANSLATION T ON 'PoStatus.'+A.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ");
            strSql.Append("  LEFT JOIN sysadm.TRANSLATION T2 ON 'PoType.'+A.PO_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY =@LANGUAGE ");
            strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
            string sql = string.Empty;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            sql = (strSql.ToString() + sql).Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            if (!onTheWayOrderRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, onTheWayOrderRptQueryEntity.CurrentPage, onTheWayOrderRptQueryEntity.PageSize, onTheWayOrderRptQueryEntity.SortField, onTheWayOrderRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + onTheWayOrderRptQueryEntity.SortField + " " + onTheWayOrderRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetOperationLogDataSet(List<string> sqlWhere, OperationLogQueryEntity operationLogQueryEntity, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" SELECT DISTINCT A.ROWID,A.WH_ID,A.[USER_ID],A.LOG_DATE,A.OPERATION_DESCR,A.UDF1,A.UDF2,A.UDF3,A.UDF4,A.UDF5,B.WH_DB_DESC,B.DB_LOGIN_ID ");
            strsql.Append(" FROM sysadm.OPERATION_LOG A LEFT OUTER JOIN sysadm.DB_CONFIG B ON A.WH_ID = B.WH_DB_ID ");
            strsql.Append(" WHERE 1=1 ");
            for (i = 0; i < sqlWhere.Count; i++)
            {
                strsql.Append(" AND " + sqlWhere[i]);
            }
            string wh_id = string.Empty;
            for (i = 0; i < whLoginID.Count; i++)
            {
                wh_id = wh_id + ((i == 0) ? "" : ",") + "'" + whLoginID[i] + "'";
            }
            if (!string.IsNullOrEmpty(wh_id))
            {
                strsql.Append(" AND B.DB_LOGIN_ID IN (" + wh_id + ")");
            }
            string StrSql = strsql.ToString();
            if (!operationLogQueryEntity.IsGetAll)
            {
                StrSql = PagingHelper.GetPagingSQL(strsql.ToString(), operationLogQueryEntity.CurrentPage, operationLogQueryEntity.PageSize, operationLogQueryEntity.SortField, operationLogQueryEntity.SortDirection);
            }
            else
            {
                StrSql = StrSql + " ORDER BY " + operationLogQueryEntity.SortField + " " + operationLogQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql);
        }

        public DataSet GetOwnerTransferRptDataSet(List<string> sqlWhere, OwnerTransferRptQueryEntity ownerTransferRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            string strSql = " SELECT A.LINE_ID,A.TRANSFER_ID,A.TRANSFER_DATE,A.FROM_OWNER_ID,A.FROM_SKU_ID,FC.DESCR AS FROM_DESCR,A.FROM_PACK_ID,A.TO_OWNER_ID,  A.TO_SKU_ID,TC.DESCR AS TO_DESCR ,A.TO_PACK_ID,  B.QTY,A.REMARK,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.EXTERNAL_LOT AS EXTERNAL_LOT,LA.VENDOR_ID AS VENDOR_ID,LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03, LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05,LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08,LA.LOT_ATTR09 AS LOT_ATTR09,LA.LOT_ATTR10 AS LOT_ATTR10 FROM @_@OWNER_TRANSFER_DETAIL A INNER JOIN  @_@TRANSFER_PICK_DETAIL B  ON A.TRANSFER_ID = B.ORDER_ID AND A.LINE_ID = B.ORDER_LINE_ID INNER JOIN @_@SKU FC ON A.FROM_SKU_ID = FC.SKU_ID AND A.FROM_OWNER_ID = FC.OWNER_ID  INNER JOIN @_@SKU TC ON A.TO_SKU_ID = TC.SKU_ID AND A.TO_OWNER_ID = TC.OWNER_ID LEFT JOIN @_@LOT_ATTRIBUTE LA  ON B.LOT = LA.LOT  INNER JOIN SYSADM.USER_OWNER UO ON A.FROM_OWNER_ID = UO.OWNER_ID  AND UO.[USER_ID] = @USER_ID  INNER JOIN SYSADM.USER_OWNER UOR ON A.TO_OWNER_ID=UOR.OWNER_ID  AND UOR.[USER_ID] = @USER_ID ";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!ownerTransferRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, ownerTransferRptQueryEntity.CurrentPage, ownerTransferRptQueryEntity.PageSize, ownerTransferRptQueryEntity.SortField, ownerTransferRptQueryEntity.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + ownerTransferRptQueryEntity.SortField + " " + ownerTransferRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPhysicalCountReportDataSet(List<string> sqlWhere, PhysicalCountDiffRptQueryEntity physicalCountDiffRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            if (physicalCountDiffRptQueryEntity.ShowLot == "N")
            {
                strSql.Append("  SELECT  PT.LOC AS LOC,PT.SKU_ID AS SKU_ID,PT.OWNER_ID AS OWNER_ID,S.DESCR AS DESCR,P.PACK_ID AS SKU_STYLE,PT.SYS_QTY AS SYS_QTY,PT.RECOUNT_QTY  AS RECOUNT_QTY,PT.ADJ_QTY AS ADJ_QTY,U.UOM_DESCR AS UOM_ID,PT.LPN_ID AS LPN_ID");
                strSql.Append("  FROM @_@PHYSICAL_TAG PT   ");
                strSql.Append("  JOIN @_@SKU S ON S.SKU_ID=PT.SKU_ID AND S.OWNER_ID=PT.OWNER_ID ");
                strSql.Append("  LEFT JOIN @_@PACK P ON S.PACK_ID=P.PACK_ID ");
                strSql.Append("  LEFT JOIN @_@UOM U ON P.PACK_UOM1=U.UOM_ID  ");
                strSql.Append("  INNER JOIN SYSADM.USER_OWNER G ON PT.OWNER_ID = G.OWNER_ID  AND G.[USER_ID] = @USER_ID  ");
                strSql.Append("  WHERE PT.ADJ_QTY != 0 AND PT.STATUS='Closed' ");
            }
            else
            {
                strSql.Append("  SELECT  PT.LOC AS LOC,PT.SKU_ID AS SKU_ID,PT.OWNER_ID AS OWNER_ID,S.DESCR AS DESCR,P.PACK_ID AS SKU_STYLE,PT.SYS_QTY AS SYS_QTY,PT.RECOUNT_QTY  AS RECOUNT_QTY,PT.ADJ_QTY AS ADJ_QTY,U.UOM_DESCR AS UOM_ID,PT.LPN_ID AS LPN_ID,PT.LOT AS LOT,LA.EXTERNAL_LOT AS EXTERNAL_LOT,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.VENDOR_ID AS VENDOR_ID,LA.RECEIPT_DATE AS RECEIPT_DATE,LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03,LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05,LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,LA.LOT_ATTR10 AS LOT_ATTR10");
                strSql.Append("  FROM @_@PHYSICAL_TAG PT   ");
                strSql.Append("  JOIN @_@SKU S ON S.SKU_ID=PT.SKU_ID AND S.OWNER_ID=PT.OWNER_ID ");
                strSql.Append("  LEFT JOIN @_@PACK P ON S.PACK_ID=P.PACK_ID ");
                strSql.Append("  LEFT JOIN @_@UOM U ON P.PACK_UOM1=U.UOM_ID  ");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE LA ON PT.LOT=LA.LOT ");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
                strSql.Append("  INNER JOIN SYSADM.USER_OWNER G ON PT.OWNER_ID = G.OWNER_ID  AND G.[USER_ID] = @USER_ID  ");
                strSql.Append("  WHERE PT.ADJ_QTY != 0 AND PT.STATUS='Closed' ");
            }
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!physicalCountDiffRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, physicalCountDiffRptQueryEntity.CurrentPage, physicalCountDiffRptQueryEntity.PageSize, physicalCountDiffRptQueryEntity.SortField, physicalCountDiffRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + physicalCountDiffRptQueryEntity.SortField + " " + physicalCountDiffRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<PhysicalCountRptInfo> GetPhysicalCountReportList(List<string> sqlWhere, PhysicalCountRptQueryEntity physicalCountRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string StrGroupby = string.Empty;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT DISTINCT L.LOC,A.SKU_ID,B.DESCR,C.PACK_ID AS SKU_STYLE,Sum(A.QTY) AS INV_QTY,U.UOM_DESCR AS UOM_ID,A.LPN_ID,B.SKU_STYLE AS SKU_STYLE2 ");
            if (physicalCountRptQueryEntity.ShowLot == "Y")
            {
                strSql.Append(",A.LOT,D.EXTERNAL_LOT,D.PRODUCE_DATE,D.EXPIRY_DATE,D.VENDOR_ID,D.RECEIPT_DATE,D.LOT_ATTR01,D.LOT_ATTR02,D.LOT_ATTR03,D.LOT_ATTR04,D.LOT_ATTR05,D.LOT_ATTR06,D.LOT_ATTR07,D.LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,D.LOT_ATTR10");
                StrGroupby = ",A.LOT,D.EXTERNAL_LOT,D.PRODUCE_DATE,D.EXPIRY_DATE,D.VENDOR_ID,D.RECEIPT_DATE,D.LOT_ATTR01,D.LOT_ATTR02,D.LOT_ATTR03,D.LOT_ATTR04,D.LOT_ATTR05,D.LOT_ATTR06,D.LOT_ATTR07,D.LOT_ATTR08,LAC.DESCR,D.LOT_ATTR10 ";
            }
            strSql.AppendFormat(" FROM @_@LOCATION L   LEFT JOIN @_@INV_LOT_LOC_LPN A  ON A.LOC=L.LOC LEFT JOIN @_@SKU B ON A.SKU_ID=B.SKU_ID AND A.OWNER_ID=B.OWNER_ID  LEFT JOIN @_@PACK C ON B.PACK_ID=C.PACK_ID LEFT JOIN @_@UOM U ON C.PACK_UOM1=U.UOM_ID   LEFT JOIN @_@LOT_ATTRIBUTE D ON A.LOT=D.LOT ", new object[0]);
            strSql.AppendFormat("  INNER JOIN SYSADM.USER_OWNER G ON (A.OWNER_ID = G.OWNER_ID OR A.OWNER_ID IS NULL) AND (G.[USER_ID] = '{0}'  OR A.OWNER_ID IS NULL) ", pubParasInfo.UserID);
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON D.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            sql = sql + " GROUP BY   L.LOC,A.SKU_ID,B.DESCR,C.PACK_ID,U.UOM_DESCR,A.LPN_ID,B.SKU_STYLE " + StrGroupby;
            if (!physicalCountRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, physicalCountRptQueryEntity.CurrentPage, physicalCountRptQueryEntity.PageSize, physicalCountRptQueryEntity.SortField, physicalCountRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'") + " ORDER BY " + physicalCountRptQueryEntity.SortField + " " + physicalCountRptQueryEntity.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<PhysicalCountRptInfo> physicalCountRptInfoList = new List<PhysicalCountRptInfo>();
            PhysicalCountRptInfo physicalCountRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        if (physicalCountRptQueryEntity.ShowLot == "Y")
                        {
                            for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                            {
                                physicalCountRptInfo = new PhysicalCountRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[2].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[2].Rows[i]["LOT"].ToString(), ds.Tables[2].Rows[i]["EXTERNAL_LOT"].ToString(), (ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["VENDOR_ID"].ToString(), (ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR10"].ToString(), ds.Tables[2].Rows[i]["UOM_ID"].ToString(), ds.Tables[2].Rows[i]["LPN_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE2"].ToString());
                                physicalCountRptInfoList.Add(physicalCountRptInfo);
                            }
                        }
                        else
                        {
                            i = 0;
                            while (i < ds.Tables[2].Rows.Count)
                            {
                                CS00003 = new DateTime();
                                CS00003 = new DateTime();
                                CS00003 = new DateTime();
                                physicalCountRptInfo = new PhysicalCountRptInfo(ds.Tables[2].Rows[i]["LOC"].ToString(), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[2].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, "", "", CS00003, CS00003, "", CS00003, "", "", "", "", "", "", "", "", "", "", ds.Tables[2].Rows[i]["UOM_ID"].ToString(), ds.Tables[2].Rows[i]["LPN_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_STYLE2"].ToString());
                                physicalCountRptInfoList.Add(physicalCountRptInfo);
                                i++;
                            }
                        }
                    }
                    if ((physicalCountRptInfoList != null) && (physicalCountRptInfoList.Count > 0))
                    {
                        physicalCountRptInfoList[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        physicalCountRptInfoList[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        physicalCountRptInfoList[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return physicalCountRptInfoList;
                }
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (physicalCountRptQueryEntity.ShowLot == "Y")
                    {
                        for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            physicalCountRptInfo = new PhysicalCountRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[0].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, ds.Tables[0].Rows[i]["LOT"].ToString(), ds.Tables[0].Rows[i]["EXTERNAL_LOT"].ToString(), (ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["VENDOR_ID"].ToString(), (ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR10"].ToString(), ds.Tables[0].Rows[i]["UOM_ID"].ToString(), ds.Tables[0].Rows[i]["LPN_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE2"].ToString());
                            physicalCountRptInfoList.Add(physicalCountRptInfo);
                        }
                    }
                    else
                    {
                        for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            CS00003 = new DateTime();
                            CS00003 = new DateTime();
                            CS00003 = new DateTime();
                            physicalCountRptInfo = new PhysicalCountRptInfo(ds.Tables[0].Rows[i]["LOC"].ToString(), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE"].ToString(), (ds.Tables[0].Rows[i]["INV_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["INV_QTY"].ToString()) : 0M, 0M, "", "", CS00003, CS00003, "", CS00003, "", "", "", "", "", "", "", "", "", "", ds.Tables[0].Rows[i]["UOM_ID"].ToString(), ds.Tables[0].Rows[i]["LPN_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_STYLE2"].ToString());
                            physicalCountRptInfoList.Add(physicalCountRptInfo);
                        }
                    }
                    physicalCountRptInfoList[0].CURRENTPAGE = "0";
                    physicalCountRptInfoList[0].TOTALPAGESCOUNT = "0";
                    physicalCountRptInfoList[0].TOTALRECORDSCOUNT = "0";
                }
            }
            return physicalCountRptInfoList;
        }

        public DataSet GetPickPickingRptInfo(List<string> sqlWhere, PickingRptQueryEntity PickingRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = string.Empty;
            string sqlOrderType = !string.IsNullOrEmpty(PickingRptQuery.ORDER_TYPE) ? (" AND  ORDER_TYPE " + PickingRptQuery.ORDER_TYPE) : "";
            string strSqlWhere = string.Empty;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSqlWhere = strSqlWhere + " AND " + sqlWhere[i].ToString();
            }
            if (PickingRptQuery.IS_GROUP == "Y")
            {
                strSql = " SELECT TEMP.ORDER_TYPE, TEMP.STATUS,TEMP.WAVE_ID, TEMP.ORDER_ID, TEMP.EXTERN_ORDER_ID, TEMP.REFERENCE_NUM, TEMP.OWNER_ID, TEMP.PACK_ID, TEMP.ORDER_LINE_ID, TEMP.ORDER_DATE,  TEMP.ORIGINAL_QTY, SUM(TEMP.QTY) AS QTY, TEMP.ORIGINAL_QTY - ISNULL(SUM(TEMP.QTY),0) AS DIFF_QTY,TEMP.REQUESTED_SHIP_DATE,  TEMP.ACTUAL_SHIP_DATE,TEMP.CREATED_BY,TEMP.UOM_DESCR, TEMP.CREATED_DATE,  TEMP.SKU_ID,  TEMP.SKU_DESCR, TEMP.SKU_CLASS_ID,    TEMP.BARCODE,    SUM(TEMP.TOTAL_GROSS_WGT) AS TOTAL_GROSS_WGT, SUM(TEMP.TOTAL_NET_WGT) AS TOTAL_NET_WGT,    SUM(TOTAL_CUBE) AS TOTAL_CUBE,   TEMP.CUSTOMER_ID,   TEMP.CUST_ADDRESS    FROM ( SELECT DISTINCT T2.[TEXT] AS ORDER_TYPE, T.[TEXT] AS STATUS, WD.WAVE_ID, ORDERS.ORDER_ID,  ORDERS.EXTERN_ORDER_ID,   ORDERS.REFERENCE_NUM,OD.ORIGINAL_QTY, PD.OWNER_ID,   ORDERS.ORDER_DATE,SKU.PACK_ID,  ORDERS.REQUESTED_SHIP_DATE, ORDERS.ACTUAL_SHIP_DATE, ORDERS.CREATED_BY,U.UOM_DESCR, ORDERS.CREATED_DATE, PD.ORDER_LINE_ID,TD.RELEASE_DATE,  PD.SKU_ID,  SKU.DESCR AS SKU_DESCR, SKU.SKU_CLASS_ID,SKU.ZONE_ID, PD.QTY as qty, PD.LPN_ID, PD.UOM_ID,  SKU.B_UDF1 AS BARCODE,   PD.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT, PD.QTY * SKU.NET_WGT AS TOTAL_NET_WGT,PD.QTY * SKU.[CUBE] AS TOTAL_CUBE,    ORDERS.CONSIGNEE_ID AS CUSTOMER_ID,   CUSTOMER.COMPANY AS CUST_NAME, CUSTOMER.ADDRESS AS CUST_ADDRESS FROM @_@PICK_DETAIL AS PD   LEFT JOIN @_@ORDERS AS ORDERS ON PD.ORDER_ID = ORDERS.ORDER_ID  LEFT JOIN @_@WAVE_DETAIL WD ON WD.ORDER_ID = ORDERS.ORDER_ID  LEFT JOIN @_@ORDER_DETAIL AS OD ON PD.ORDER_ID = OD.ORDER_ID AND PD.ORDER_LINE_ID = OD.LINE_ID  LEFT JOIN @_@SKU AS SKU ON PD.OWNER_ID = SKU.OWNER_ID AND PD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID  LEFT JOIN @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1  LEFT JOIN @_@CUSTOMER AS CUSTOMER ON ORDERS.CONSIGNEE_ID = CUSTOMER.CUSTOMER_ID   LEFT JOIN @_@TASK_DETAIL AS TD ON PD.PICK_DETAIL_ID = TD.PICK_DETAIL_ID  LEFT JOIN @_@LOT_ATTRIBUTE AS LA ON PD.LOT = LA.LOT   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PD.STATUS AND SCD.SYS_CODE_TYPE = 'PickDetailStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = ORDERS.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'PickDetailStatus.'+PD.STATUS = T.ITEM_ID AND T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+ORDERS.TYPE = T2.ITEM_ID AND T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON ORDERS.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE 1 = 1 " + strSqlWhere + " ) TEMP   WHERE (CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = 'admin') OR CUSTOMER_ID IS NULL OR CUSTOMER_ID = '') " + sqlOrderType;
            }
            else
            {
                strSql = " SELECT DISTINCT * FROM (   SELECT DISTINCT PD.PICK_DETAIL_ID, T2.[TEXT] AS ORDER_TYPE, T.[TEXT] AS STATUS, WD.WAVE_ID, ORDERS.ORDER_ID,  ORDERS.EXTERN_ORDER_ID,  ORDERS.REFERENCE_NUM,  PD.OWNER_ID,    ORDERS.ORDER_DATE,  ORDERS.REQUESTED_SHIP_DATE, ORDERS.ACTUAL_SHIP_DATE, ORDERS.CREATED_BY, ORDERS.CREATED_DATE, PD.ORDER_LINE_ID,TD.RELEASE_DATE, CASE PD.STATUS WHEN 'NEW' THEN NULL   ELSE PD.UPDATED_DATE END AS PICK_DETAIL_CONFIRM_DATE,  PD.SKU_ID,  SKU.DESCR AS SKU_DESCR, SKU.SKU_CLASS_ID,SKU.ZONE_ID, PD.QTY,  PD.UOM_ID,   PD.LOC, PD.LPN_ID, LAC.DESCR AS SKU_PROPERTY, SKU.B_UDF1 AS BARCODE,    PD.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT, PD.QTY * SKU.NET_WGT AS TOTAL_NET_WGT,   PD.QTY * SKU.[CUBE] AS TOTAL_CUBE,   ORDERS.CONSIGNEE_ID AS CUSTOMER_ID,   CUSTOMER.COMPANY AS CUST_NAME, CUSTOMER.ADDRESS AS CUST_ADDRESS, LA.LOT_ATTR01,   LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08,  LAC.DESCR AS LOT_ATTR09,   LA.LOT_ATTR10,  LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.EXTERNAL_LOT,LA.RECEIPT_DATE, VR.VENDOR_ID, VR.COMPANY AS VENDOR_COMPANY ,ORDERS.REMARK AS REMARK   FROM @_@PICK_DETAIL AS PD LEFT JOIN   @_@ORDERS AS ORDERS ON PD.ORDER_ID = ORDERS.ORDER_ID LEFT OUTER JOIN   @_@WAVE_DETAIL WD ON WD.ORDER_ID = ORDERS.ORDER_ID LEFT JOIN  @_@SKU AS SKU ON PD.OWNER_ID = SKU.OWNER_ID AND PD.SKU_ID = SKU.SKU_ID LEFT OUTER JOIN   @_@CUSTOMER AS CUSTOMER ON ORDERS.CONSIGNEE_ID = CUSTOMER.CUSTOMER_ID LEFT OUTER JOIN   @_@TASK_DETAIL AS TD ON PD.PICK_DETAIL_ID = TD.PICK_DETAIL_ID LEFT OUTER JOIN   @_@LOT_ATTRIBUTE AS LA ON PD.LOT = LA.LOT   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PD.STATUS AND SCD.SYS_CODE_TYPE = 'PickDetailStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = ORDERS.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'PickDetailStatus.'+PD.STATUS = T.ITEM_ID           AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+ORDERS.TYPE = T2.ITEM_ID           AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@VENDOR AS VR ON LA.VENDOR_ID = VR.VENDOR_ID INNER JOIN SYSADM.USER_OWNER UO ON ORDERS.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'  WHERE 1 = 1 " + strSqlWhere + " ) TEMP  WHERE (CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR CUSTOMER_ID IS NULL OR CUSTOMER_ID = '') " + sqlOrderType;
            }
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            if (PickingRptQuery.IS_GROUP == "Y")
            {
                sql = sql + " GROUP BY  TEMP.ORDER_TYPE, TEMP.STATUS,TEMP.WAVE_ID, TEMP.ORDER_ID, TEMP.EXTERN_ORDER_ID, TEMP.REFERENCE_NUM, TEMP.OWNER_ID,TEMP.PACK_ID, TEMP.ORDER_LINE_ID, TEMP.ORDER_DATE, TEMP.ORIGINAL_QTY,TEMP.REQUESTED_SHIP_DATE,  TEMP.ACTUAL_SHIP_DATE,TEMP.CREATED_BY,TEMP.UOM_DESCR, TEMP.CREATED_DATE,  TEMP.SKU_ID,  TEMP.SKU_DESCR, TEMP.SKU_CLASS_ID,  TEMP.BARCODE,  TEMP.CUSTOMER_ID,   TEMP.CUST_ADDRESS   ";
            }
            if (!PickingRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, PickingRptQuery.CurrentPage, PickingRptQuery.PageSize, PickingRptQuery.SortField, PickingRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + PickingRptQuery.SortField + " " + PickingRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPurchasePlanRptDataSet(List<string> sqlWhere, PurchasePlanRptQueryEntity purchasePlanRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("    SELECT INV.OWNER_ID,INV.SKU_ID,INV.DESCR,INV.SKU_CLASS_ID,INV.MIN_QTY,INV.MAX_QTY,INV.REORDER_QTY,INV.PACK_ID,INV.UOM_DESCR,ISNULL(SUM(INV.INVQTY),0) AS INVQTY,ISNULL(SUM(POD.RECEIVED_QTY),0) AS RECEIVED_QTY ,ISNULL(SUM(POD.ORDERED_QTY)-SUM(POD.RECEIVED_QTY),0) AS TRANSIT_QTY FROM  ");
            strSql.Append("   (SELECT S.OWNER_ID,S.SKU_ID,S.DESCR,S.SKU_CLASS_ID,S.MIN_QTY,S.MAX_QTY,S.REORDER_QTY,SUM(INV.QTY) AS INVQTY,S.PACK_ID,U.UOM_DESCR ");
            strSql.Append("    FROM  @_@SKU S ");
            strSql.Append("  LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID ");
            strSql.Append("  LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1 ");
            strSql.Append("  LEFT JOIN  @_@INV_LOT INV ON S.OWNER_ID=INV.OWNER_ID AND S.SKU_ID = INV.SKU_ID  ");
            strSql.Append("    INNER JOIN SYSADM.USER_OWNER UO ON S.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID  WHERE S.REORDER_QTY >0 AND S.IS_ACTIVE='Y'");
            strSql.Append("    GROUP BY  S.OWNER_ID,S.SKU_ID,S.DESCR,S.SKU_CLASS_ID,S.MIN_QTY,S.MAX_QTY,S.REORDER_QTY,S.PACK_ID,U.UOM_DESCR ) INV ");
            strSql.Append("   LEFT JOIN  @_@PURCHASE_ORDER_DETAIL POD ON INV.OWNER_ID=POD.OWNER_ID AND INV.SKU_ID = POD.SKU_ID AND (POD.STATUS ='NEW' OR POD.STATUS='INRECEIVING') ");
            string sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE  " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            sql = sql + " GROUP BY INV.OWNER_ID,INV.SKU_ID,INV.DESCR,INV.SKU_CLASS_ID,INV.MIN_QTY,INV.MAX_QTY,INV.REORDER_QTY,INV.PACK_ID,INV.UOM_DESCR  HAVING  ISNULL(SUM(INV.INVQTY),0)+(ISNULL(SUM(POD.ORDERED_QTY),0)-ISNULL(SUM(POD.RECEIVED_QTY),0))-ISNULL(INV.MIN_QTY,0 )<=0 ";
            if (!purchasePlanRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, purchasePlanRptQueryEntity.CurrentPage, purchasePlanRptQueryEntity.PageSize, purchasePlanRptQueryEntity.SortField, purchasePlanRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + purchasePlanRptQueryEntity.SortField + " " + purchasePlanRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<ReceiptDetailRptInfo> GetReceiptDetailRptInfo(List<string> sqlWhere, ReceiptDetailRptQueryEntity receiptDetailRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            StringBuilder sSQL = new StringBuilder();
            if (receiptDetailRptQueryEntity.SHOW_LPN)
            {
                sSQL.Append(" SELECT DISTINCT * ");
            }
            else
            {
                sSQL.Append(" SELECT RECEIPT_ID, RECEIPT_LINE_ID, PO_ID, OWNER_ID, EXTERN_RECEIPT_ID, RD_STATUS, STATUS_D, RECEIPT_TYPE_D, [STATUS], RECEIPT_TYPE, RECEIPT_DATE, SKU_ID, SKU_DESCR, ");
                sSQL.Append(" SUM(EXPECTED_QTY) AS EXPECTED_QTY, SUM(RECEIVED_QTY) AS RECEIVED_QTY, CONDITION_CODE, '' AS TO_LPN_ID, TO_LOT, VENDOR_ID, VENDOR_NAME, SKU_PROPERTY, RMA, ");
                sSQL.Append(" LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, [EXPIRY_DATE], EXTERNAL_LOT, ");
                sSQL.Append(" REMARK, SKU_CLASS_ID, SKU_CLASS_DESCR, SKU_GROUP, PACK_ID, '' AS SKU_UDF2, SKU_STYLE, UOM_DESCR, '' AS TRANS_ID, UNIT_PRICE, ");
                sSQL.Append(" SUM(PRICE) AS PRICE, SUM(TOTAL_GROSS_WGT) AS TOTAL_GROSS_WGT, SUM(TOTAL_CUBE) AS TOTAL_CUBE, SUM(TOTAL_WGT) AS TOTAL_WGT ");
            }
            sSQL.Append(" FROM ( ");
            sSQL.Append(" SELECT RD.RECEIPT_ID,RD.RECEIPT_LINE_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID AS EXTERN_RECEIPT_ID,RD.STATUS AS RD_STATUS,T.[TEXT] AS STATUS_D,T2.[TEXT] AS RECEIPT_TYPE_D,RD.STATUS,R.RECEIPT_TYPE,");
            sSQL.Append(" IT.TRANS_DATE AS RECEIPT_DATE,RD.SKU_ID,SKU.DESCR AS SKU_DESCR,RD.EXPECTED_QTY,IT.QTY AS RECEIVED_QTY,IT.HOLD_CODE AS CONDITION_CODE,IT.TO_LPN_ID,IT.LOT AS TO_LOT,IT.VENDOR_ID,V.COMPANY AS VENDOR_NAME,LAC.DESCR AS SKU_PROPERTY, R.RMA,");
            sSQL.Append(" IT.LOT_ATTR01,IT.LOT_ATTR02,IT.LOT_ATTR03,IT.LOT_ATTR04,IT.LOT_ATTR05,IT.LOT_ATTR06,IT.LOT_ATTR07,IT.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09,IT.LOT_ATTR10,IT.PRODUCE_DATE,IT.EXPIRY_DATE,IT.EXTERNAL_LOT,");
            sSQL.Append(" RD.REMARK,SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR,SKU.SKU_GROUP,SKU.PACK_ID,SKU.UDF2 AS SKU_UDF2,RD.UNIT_PRICE,ISNULL(IT.QTY * RD.UNIT_PRICE,0) AS PRICE,");
            sSQL.Append(" IT.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,IT.QTY * SKU.[CUBE] AS TOTAL_CUBE,IT.QTY * SKU.NET_WGT AS TOTAL_WGT,SKU.SKU_STYLE,U.UOM_DESCR,IT.TRANS_ID   ");
            sSQL.Append(" FROM @_@RECEIPT_DETAIL RD  ");
            sSQL.Append(" LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID   ");
            sSQL.Append(" LEFT JOIN @_@INV_TRANS IT ON RD.RECEIPT_ID = IT.RECEIPT_ID AND RD.RECEIPT_LINE_ID = IT.RECEIPT_LINE_ID ");
            sSQL.Append(" AND IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'ReceiptDetailAdd'  AND IT.STATUS = 'OK' ");
            sSQL.Append(" LEFT JOIN @_@VENDOR V ON IT.VENDOR_ID = V.VENDOR_ID");
            sSQL.Append(" LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID   ");
            sSQL.Append(" LEFT JOIN @_@PACK P ON P.PACK_ID = SKU.PACK_ID ");
            sSQL.Append(" LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1 ");
            sSQL.Append(" LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID");
            sSQL.Append(" LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' ");
            sSQL.Append(" LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' ");
            sSQL.Append(" LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ");
            sSQL.Append(" LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE");
            sSQL.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' ");
            sSQL.Append(" INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            sSQL.Append(" WHERE R.IS_PALLETIZED = 'N' AND RD.STATUS IN ('InReceiving','ReceivedComplete','CLOSED') ");
            for (i = 0; i < sqlWhere.Count; i++)
            {
                sSQL.Append(" AND " + sqlWhere[i].ToString());
            }
            sSQL.Append(" UNION ALL ");
            sSQL.Append(" SELECT RD.RECEIPT_ID,RD.RECEIPT_LINE_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID AS EXTERN_RECEIPT_ID,RD.STATUS AS RD_STATUS,T.[TEXT] AS STATUS_D,T2.[TEXT] AS RECEIPT_TYPE_D,RD.STATUS,R.RECEIPT_TYPE,");
            sSQL.Append(" IT.TRANS_DATE AS RECEIPT_DATE,RD.SKU_ID,SKU.DESCR AS SKU_DESCR,RD.EXPECTED_QTY,IT.QTY AS RECEIVED_QTY,IT.HOLD_CODE AS CONDITION_CODE,IT.TO_LPN_ID,IT.LOT AS TO_LOT,IT.VENDOR_ID,V.COMPANY AS VENDOR_NAME,LAC.DESCR AS SKU_PROPERTY, R.RMA,");
            sSQL.Append(" IT.LOT_ATTR01,IT.LOT_ATTR02,IT.LOT_ATTR03,IT.LOT_ATTR04,IT.LOT_ATTR05,IT.LOT_ATTR06,IT.LOT_ATTR07,IT.LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,IT.LOT_ATTR10,IT.PRODUCE_DATE,IT.EXPIRY_DATE,IT.EXTERNAL_LOT,");
            sSQL.Append(" RD.REMARK,SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR,SKU.SKU_GROUP,SKU.PACK_ID,SKU.UDF2 AS SKU_UDF2,RD.UNIT_PRICE,ISNULL(IT.QTY * RD.UNIT_PRICE,0) AS PRICE,");
            sSQL.Append(" IT.QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,IT.QTY * SKU.[CUBE] AS TOTAL_CUBE,IT.QTY * SKU.NET_WGT AS TOTAL_WGT,SKU.SKU_STYLE,U.UOM_DESCR ,IT.TRANS_ID   ");
            sSQL.Append(" FROM @_@RECEIPT_DETAIL_LPN RD  ");
            sSQL.Append(" LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID   ");
            sSQL.Append(" LEFT JOIN @_@INV_TRANS IT ON RD.RECEIPT_ID = IT.RECEIPT_ID AND RD.RECEIPT_LINE_ID = IT.RECEIPT_LINE_ID ");
            sSQL.Append(" AND IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'ReceiptDetailAdd'  AND IT.STATUS = 'OK' ");
            sSQL.Append(" LEFT JOIN @_@VENDOR V ON IT.VENDOR_ID = V.VENDOR_ID");
            sSQL.Append(" LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID   ");
            sSQL.Append(" LEFT JOIN @_@PACK P ON P.PACK_ID = SKU.PACK_ID ");
            sSQL.Append(" LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1 ");
            sSQL.Append(" LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID");
            sSQL.Append(" LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' ");
            sSQL.Append(" LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' ");
            sSQL.Append(" LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ");
            sSQL.Append(" LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID AND T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE");
            sSQL.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' ");
            sSQL.Append(" INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            sSQL.Append(" WHERE R.IS_PALLETIZED = 'Y' AND RD.STATUS IN ('InReceiving','ReceivedComplete','CLOSED') ");
            for (i = 0; i < sqlWhere.Count; i++)
            {
                sSQL.Append(" AND " + sqlWhere[i].ToString());
            }
            if (receiptDetailRptQueryEntity.SHOW_LPN)
            {
                sSQL.Append(" ) TEMP WHERE (VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE [USER_ID] = @USER_ID) OR ISNULL(VENDOR_ID,'') = '')");
            }
            else
            {
                sSQL.Append(" ) TEMP WHERE (VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE [USER_ID] = @USER_ID) OR ISNULL(VENDOR_ID,'') = '')");
                sSQL.Append(" GROUP BY RECEIPT_ID, RECEIPT_LINE_ID, PO_ID, OWNER_ID, EXTERN_RECEIPT_ID, RD_STATUS, STATUS_D, RECEIPT_TYPE_D, [STATUS], RECEIPT_TYPE, RECEIPT_DATE, SKU_ID, SKU_DESCR, ");
                sSQL.Append(" CONDITION_CODE, TO_LOT, VENDOR_ID, VENDOR_NAME, SKU_PROPERTY, RMA, ");
                sSQL.Append(" LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, [EXPIRY_DATE], EXTERNAL_LOT, ");
                sSQL.Append(" REMARK, SKU_CLASS_ID, SKU_CLASS_DESCR, SKU_GROUP, PACK_ID, SKU_STYLE, UOM_DESCR, UNIT_PRICE ");
            }
            string sql = sSQL.ToString().Replace("@_@", whLoginID);
            if (!receiptDetailRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, receiptDetailRptQueryEntity.CurrentPage, receiptDetailRptQueryEntity.PageSize, receiptDetailRptQueryEntity.SortField, receiptDetailRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + receiptDetailRptQueryEntity.SortField + " " + receiptDetailRptQueryEntity.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<ReceiptDetailRptInfo> receiptDetailRptInfos = new List<ReceiptDetailRptInfo>();
            ReceiptDetailRptInfo receiptDetailRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            receiptDetailRptInfo = new ReceiptDetailRptInfo(ds.Tables[2].Rows[i]["RECEIPT_ID"].ToString(), int.Parse(string.IsNullOrEmpty(ds.Tables[2].Rows[i]["RECEIPT_LINE_ID"].ToString()) ? "0" : ds.Tables[2].Rows[i]["RECEIPT_LINE_ID"].ToString()), ds.Tables[2].Rows[i]["PO_ID"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["EXTERN_RECEIPT_ID"].ToString(), ds.Tables[2].Rows[i]["STATUS_D"].ToString(), ds.Tables[2].Rows[i]["RECEIPT_TYPE_D"].ToString(), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR"].ToString(), decimal.Parse(ds.Tables[2].Rows[i]["EXPECTED_QTY"].ToString()), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["RECEIVED_QTY"].ToString()) ? 0M : decimal.Parse(ds.Tables[2].Rows[i]["RECEIVED_QTY"].ToString()), ds.Tables[2].Rows[i]["CONDITION_CODE"].ToString(), ds.Tables[2].Rows[i]["TO_LPN_ID"].ToString(), ds.Tables[2].Rows[i]["TO_LOT"].ToString(), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["TOTAL_WGT"].ToString()) ? 0f : float.Parse(ds.Tables[2].Rows[i]["TOTAL_WGT"].ToString()), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["TOTAL_CUBE"].ToString()) ? 0f : float.Parse(ds.Tables[2].Rows[i]["TOTAL_CUBE"].ToString()), ds.Tables[2].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[2].Rows[i]["RMA"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[2].Rows[i]["LOT_ATTR10"].ToString(), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[2].Rows[i]["PRODUCE_DATE"].ToString()), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[2].Rows[i]["EXPIRY_DATE"].ToString()), ds.Tables[2].Rows[i]["EXTERNAL_LOT"].ToString(), ds.Tables[2].Rows[i]["REMARK"].ToString(), ds.Tables[2].Rows[i]["SKU_CLASS_ID"].ToString(), ds.Tables[2].Rows[i]["VENDOR_NAME"].ToString(), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["TOTAL_GROSS_WGT"].ToString()) ? 0M : decimal.Parse(ds.Tables[2].Rows[i]["TOTAL_GROSS_WGT"].ToString()), ds.Tables[2].Rows[i]["SKU_CLASS_DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_PROPERTY"].ToString(), ds.Tables[2].Rows[i]["SKU_GROUP"].ToString(), ds.Tables[2].Rows[i]["PACK_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_UDF2"].ToString(), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["UNIT_PRICE"].ToString()) ? 0M : decimal.Parse(ds.Tables[2].Rows[i]["UNIT_PRICE"].ToString()), string.IsNullOrEmpty(ds.Tables[2].Rows[i]["PRICE"].ToString()) ? 0M : decimal.Parse(ds.Tables[2].Rows[i]["PRICE"].ToString()), ds.Tables[2].Rows[i]["SKU_STYLE"].ToString(), ds.Tables[2].Rows[i]["UOM_DESCR"].ToString());
                            receiptDetailRptInfos.Add(receiptDetailRptInfo);
                        }
                    }
                    if ((receiptDetailRptInfos != null) && (receiptDetailRptInfos.Count > 0))
                    {
                        receiptDetailRptInfos[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        receiptDetailRptInfos[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        receiptDetailRptInfos[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return receiptDetailRptInfos;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return receiptDetailRptInfos;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    receiptDetailRptInfo = new ReceiptDetailRptInfo(ds.Tables[0].Rows[i]["RECEIPT_ID"].ToString(), int.Parse(string.IsNullOrEmpty(ds.Tables[0].Rows[i]["RECEIPT_LINE_ID"].ToString()) ? "0" : ds.Tables[0].Rows[i]["RECEIPT_LINE_ID"].ToString()), ds.Tables[0].Rows[i]["PO_ID"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["EXTERN_RECEIPT_ID"].ToString(), ds.Tables[0].Rows[i]["STATUS_D"].ToString(), ds.Tables[0].Rows[i]["RECEIPT_TYPE_D"].ToString(), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR"].ToString(), decimal.Parse(ds.Tables[0].Rows[i]["EXPECTED_QTY"].ToString()), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["RECEIVED_QTY"].ToString()) ? 0M : decimal.Parse(ds.Tables[0].Rows[i]["RECEIVED_QTY"].ToString()), ds.Tables[0].Rows[i]["CONDITION_CODE"].ToString(), ds.Tables[0].Rows[i]["TO_LPN_ID"].ToString(), ds.Tables[0].Rows[i]["TO_LOT"].ToString(), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["TOTAL_WGT"].ToString()) ? 0f : float.Parse(ds.Tables[0].Rows[i]["TOTAL_WGT"].ToString()), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["TOTAL_CUBE"].ToString()) ? 0f : float.Parse(ds.Tables[0].Rows[i]["TOTAL_CUBE"].ToString()), ds.Tables[0].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[0].Rows[i]["RMA"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR01"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR02"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR03"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR04"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR05"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR06"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR07"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR08"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR09"].ToString(), ds.Tables[0].Rows[i]["LOT_ATTR10"].ToString(), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[0].Rows[i]["PRODUCE_DATE"].ToString()), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString()) ? (CS00003 = new DateTime()) : DateTime.Parse(ds.Tables[0].Rows[i]["EXPIRY_DATE"].ToString()), ds.Tables[0].Rows[i]["EXTERNAL_LOT"].ToString(), ds.Tables[0].Rows[i]["REMARK"].ToString(), ds.Tables[0].Rows[i]["SKU_CLASS_ID"].ToString(), ds.Tables[0].Rows[i]["VENDOR_NAME"].ToString(), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["TOTAL_GROSS_WGT"].ToString()) ? 0M : decimal.Parse(ds.Tables[0].Rows[i]["TOTAL_GROSS_WGT"].ToString()), ds.Tables[0].Rows[i]["SKU_CLASS_DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_PROPERTY"].ToString(), ds.Tables[0].Rows[i]["SKU_GROUP"].ToString(), ds.Tables[0].Rows[i]["PACK_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_UDF2"].ToString(), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["UNIT_PRICE"].ToString()) ? 0M : decimal.Parse(ds.Tables[0].Rows[i]["UNIT_PRICE"].ToString()), string.IsNullOrEmpty(ds.Tables[0].Rows[i]["PRICE"].ToString()) ? 0M : decimal.Parse(ds.Tables[0].Rows[i]["PRICE"].ToString()), ds.Tables[0].Rows[i]["SKU_STYLE"].ToString(), ds.Tables[0].Rows[i]["UOM_DESCR"].ToString());
                    receiptDetailRptInfos.Add(receiptDetailRptInfo);
                }
                receiptDetailRptInfos[0].CURRENTPAGE = "0";
                receiptDetailRptInfos[0].TOTALPAGESCOUNT = "0";
                receiptDetailRptInfos[0].TOTALRECORDSCOUNT = "0";
            }
            return receiptDetailRptInfos;
        }

        public List<ReceiptRptInfo> GetReceiptRptInfo(List<string> sqlWhere, ReceiptRptQueryEntity ReceiptRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSql = " SELECT RECEIPT_ID,PO_ID,OWNER_ID,EXTERNAL_RECEIPT_ID,STATUS,RECEIPT_TYPE,VENDOR_ID,VENDOR_NAME,RECEIVED_DATE,RECEIPT_DATE,CREATED_DATE,REMARK,SKU_ID,SKU_DESCR,RMA,SKU_CLASS_ID,SKU_CLASS_DESCR,  SUM(EXPECTED_QTY) AS EXPECTED_QTY,SUM(RECEIVED_QTY) AS RECEIVED_QTY,SUM(DIFF_QTY) AS DIFF_QTY,SUM(TOTAL_GROSS_WGT) AS TOTAL_GROSS_WGT,SUM(TOTAL_CUBE) AS TOTAL_CUBE,SUM(TOTAL_WGT) AS TOTAL_WGT,PACK_ID,UOM_DESCR  FROM (  SELECT RD.RECEIPT_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID,T.[TEXT] AS STATUS,T2.[TEXT] AS RECEIPT_TYPE,RD.VENDOR_ID,RD.VENDOR_NAME,RD.RECEIVED_DATE,RD.RECEIPT_DATE,RD.CREATED_DATE,R.REMARK,RD.SKU_ID,SKU.DESCR AS SKU_DESCR,RD.EXPECTED_QTY,RD.RECEIVED_QTY,(RD.EXPECTED_QTY*SKU.GROSS_WGT) AS TOTAL_GROSS_WGT,(RD.EXPECTED_QTY*SKU.[CUBE]) AS TOTAL_CUBE,RD.RMA,SKU.SKU_CLASS_ID,(RD.EXPECTED_QTY - RD.RECEIVED_QTY) AS DIFF_QTY,(RD.EXPECTED_QTY*SKU.NET_WGT) AS TOTAL_WGT,SC.DESCR AS SKU_CLASS_DESCR,SKU.PACK_ID,U.UOM_DESCR  FROM @_@RECEIPT_DETAIL RD LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID   LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID    LEFT JOIN @_@PACK P ON P.PACK_ID = SKU.PACK_ID  LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1  LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SC.OWNER_ID = SKU.OWNER_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID AND T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID AND T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE  LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON RD.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE R.IS_PALLETIZED = 'N'  UNION ALL  SELECT RD.RECEIPT_ID,RD.PO_ID,RD.OWNER_ID,R.EXTERNAL_RECEIPT_ID,T.[TEXT] AS STATUS,T2.[TEXT] AS RECEIPT_TYPE,RD.VENDOR_ID,RD.VENDOR_NAME,RD.RECEIVED_DATE,RD.RECEIPT_DATE,RD.CREATED_DATE,R.REMARK,RD.SKU_ID,SKU.DESCR AS SKU_DESCR,RD.EXPECTED_QTY,RD.RECEIVED_QTY,(RD.EXPECTED_QTY*SKU.GROSS_WGT) AS TOTAL_GROSS_WGT,(RD.EXPECTED_QTY*SKU.[CUBE]) AS TOTAL_CUBE,RD.RMA,SKU.SKU_CLASS_ID,(RD.EXPECTED_QTY - RD.RECEIVED_QTY) AS DIFF_QTY,(RD.EXPECTED_QTY*SKU.NET_WGT) AS TOTAL_WGT,SC.DESCR AS SKU_CLASS_DESCR,SKU.PACK_ID,U.UOM_DESCR  FROM @_@RECEIPT_DETAIL_LPN RD LEFT JOIN @_@RECEIPT R ON R.RECEIPT_ID = RD.RECEIPT_ID   LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = RD.OWNER_ID AND SKU.SKU_ID = RD.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = SKU.PACK_ID  LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1  LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SC.OWNER_ID = SKU.OWNER_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = R.RECEIPT_TYPE AND SCD2.SYS_CODE_TYPE = 'ReceiptType' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID           AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID           AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON RD.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' INNER JOIN SYSADM.USER_OWNER UO ON R.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID    WHERE R.IS_PALLETIZED = 'Y'  ) TEMP WHERE (VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE [USER_ID] = @USER_ID) OR VENDOR_ID IS NULL OR VENDOR_ID = '') ";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            sql = sql + " GROUP BY RECEIPT_ID,PO_ID,OWNER_ID,EXTERNAL_RECEIPT_ID,STATUS,RECEIPT_TYPE,VENDOR_ID,VENDOR_NAME,RECEIVED_DATE,RECEIPT_DATE,CREATED_DATE,REMARK,SKU_ID,SKU_DESCR,RMA,SKU_CLASS_ID,SKU_CLASS_DESCR,PACK_ID,UOM_DESCR";
            if (!ReceiptRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, ReceiptRptQuery.CurrentPage, ReceiptRptQuery.PageSize, ReceiptRptQuery.SortField, ReceiptRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + ReceiptRptQuery.SortField + " " + ReceiptRptQuery.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<ReceiptRptInfo> receiptRptInfos = new List<ReceiptRptInfo>();
            ReceiptRptInfo receiptRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            receiptRptInfo = new ReceiptRptInfo(ds.Tables[2].Rows[i]["RECEIPT_ID"].ToString(), ds.Tables[2].Rows[i]["PO_ID"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["EXTERNAL_RECEIPT_ID"].ToString(), ds.Tables[2].Rows[i]["RECEIPT_TYPE"].ToString(), ds.Tables[2].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[2].Rows[i]["VENDOR_NAME"].ToString(), (ds.Tables[2].Rows[i]["CREATED_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["REMARK"].ToString(), ds.Tables[2].Rows[i]["SKU_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[2].Rows[i]["SKU_CLASS_ID"].ToString(), ds.Tables[2].Rows[i]["SKU_CLASS_DESCR"].ToString(), ds.Tables[2].Rows[i]["RMA"].ToString(), (ds.Tables[2].Rows[i]["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["EXPECTED_QTY"].ToString()) : 0M, (ds.Tables[2].Rows[i]["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["RECEIVED_QTY"].ToString()) : 0M, (ds.Tables[2].Rows[i]["DIFF_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["DIFF_QTY"].ToString()) : 0M, (ds.Tables[2].Rows[i]["TOTAL_GROSS_WGT"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["TOTAL_GROSS_WGT"].ToString()) : 0M, (ds.Tables[2].Rows[i]["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["TOTAL_CUBE"].ToString()) : 0M, (ds.Tables[2].Rows[i]["TOTAL_WGT"].ToString() != "") ? decimal.Parse(ds.Tables[2].Rows[i]["TOTAL_WGT"].ToString()) : 0M, ds.Tables[2].Rows[i]["PACK_ID"].ToString(), ds.Tables[2].Rows[i]["UOM_DESCR"].ToString());
                            receiptRptInfos.Add(receiptRptInfo);
                        }
                    }
                    if ((receiptRptInfos != null) && (receiptRptInfos.Count > 0))
                    {
                        receiptRptInfos[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        receiptRptInfos[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        receiptRptInfos[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return receiptRptInfos;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return receiptRptInfos;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    receiptRptInfo = new ReceiptRptInfo(ds.Tables[0].Rows[i]["RECEIPT_ID"].ToString(), ds.Tables[0].Rows[i]["PO_ID"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["EXTERNAL_RECEIPT_ID"].ToString(), ds.Tables[0].Rows[i]["RECEIPT_TYPE"].ToString(), ds.Tables[0].Rows[i]["VENDOR_ID"].ToString(), ds.Tables[0].Rows[i]["VENDOR_NAME"].ToString(), (ds.Tables[0].Rows[i]["CREATED_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["REMARK"].ToString(), ds.Tables[0].Rows[i]["SKU_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_DESCR"].ToString(), ds.Tables[0].Rows[i]["SKU_CLASS_ID"].ToString(), ds.Tables[0].Rows[i]["SKU_CLASS_DESCR"].ToString(), ds.Tables[0].Rows[i]["RMA"].ToString(), (ds.Tables[0].Rows[i]["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["EXPECTED_QTY"].ToString()) : 0M, (ds.Tables[0].Rows[i]["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["RECEIVED_QTY"].ToString()) : 0M, (ds.Tables[0].Rows[i]["DIFF_QTY"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["DIFF_QTY"].ToString()) : 0M, (ds.Tables[0].Rows[i]["TOTAL_GROSS_WGT"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["TOTAL_GROSS_WGT"].ToString()) : 0M, (ds.Tables[0].Rows[i]["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["TOTAL_CUBE"].ToString()) : 0M, (ds.Tables[0].Rows[i]["TOTAL_WGT"].ToString() != "") ? decimal.Parse(ds.Tables[0].Rows[i]["TOTAL_WGT"].ToString()) : 0M, ds.Tables[0].Rows[i]["PACK_ID"].ToString(), ds.Tables[0].Rows[i]["UOM_DESCR"].ToString());
                    receiptRptInfos.Add(receiptRptInfo);
                }
                receiptRptInfos[0].CURRENTPAGE = "0";
                receiptRptInfos[0].TOTALPAGESCOUNT = "0";
                receiptRptInfos[0].TOTALRECORDSCOUNT = "0";
            }
            return receiptRptInfos;
        }

        public DataSet GetSafeInventoryRptInfo(List<string> sqlWhere, SafeInventoryRptQueryEntity safeInventoryRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = ("SELECT DISTINCT A.OWNER_ID AS OWNER_ID, A.SKU_ID AS SKU_ID,A.SKU_CLASS_ID AS SKU_CLASS_ID, A.DESCR AS DESCR, A.DESCR2 AS DESCR2, ISNULL(A.MIN_QTY,0) AS MIN_QTY, ISNULL(A.MAX_QTY,0) AS MAX_QTY," + (safeInventoryRptQuery.SHOW_VENDOR ? "LA.VENDOR_ID, " : " '' AS VENDOR_ID,") + " ISNULL(A.REORDER_POINT,0) AS REORDER_POINT, SUM(ISNULL(B.QTY,0)) AS QTY, SUM(ISNULL(B.PREALLOCATED_QTY,0)) AS PREALLOCATED_QTY, SUM((ISNULL(B.QTY,0) - ISNULL(B.PREALLOCATED_QTY,0))) AS AVAILABLE_QTY,A.PACK_ID,U.UOM_DESCR, MAX(LA.RECEIPT_DATE) AS LAST_RECEIPT_DATE,DATEDIFF(DAY, MAX(LA.RECEIPT_DATE), GETDATE()) AS LAST_DAYS,  SUM(CASE WHEN DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) < 30 THEN ISNULL(B.QTY,0) END) AS DAYS_1,  SUM(CASE WHEN DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) >= 30 AND DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) < 60 THEN ISNULL(B.QTY,0) END) AS DAYS_30,  SUM(CASE WHEN DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) >= 60 AND DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) < 90 THEN ISNULL(B.QTY,0) END) AS DAYS_60,  SUM(CASE WHEN DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) >= 90 AND DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) < 120 THEN ISNULL(B.QTY,0) END) AS DAYS_90,  SUM(CASE WHEN DATEDIFF(DAY, LA.RECEIPT_DATE, GETDATE()) >= 120 THEN ISNULL(B.QTY,0) END) AS DAYS_120  FROM @_@SKU AS A  LEFT JOIN  @_@PACK PACK ON A.PACK_ID = PACK.PACK_ID  LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1  LEFT JOIN  @_@INV_LOT  AS B  ON A.SKU_ID=B.SKU_ID AND A.OWNER_ID = B.OWNER_ID  LEFT JOIN  @_@LOT_ATTRIBUTE LA ON LA.OWNER_ID = A.OWNER_ID AND LA.SKU_ID = A.SKU_ID AND LA.LOT = B.LOT  INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE (ISNULL(A.MIN_QTY,0) > 0 OR ISNULL(A.MAX_QTY,0) > 0)  AND A.IS_ACTIVE='Y' ").ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (safeInventoryRptQuery.SHOW_ALL)
            {
                sql = sql + " AND B.QTY > 0 ";
            }
            sql = sql + " GROUP BY A.OWNER_ID, A.SKU_ID,A.SKU_CLASS_ID, A.DESCR, A.DESCR2, A.MIN_QTY, A.MAX_QTY, A.REORDER_POINT,A.PACK_ID,U.UOM_DESCR " + (safeInventoryRptQuery.SHOW_VENDOR ? ",LA.VENDOR_ID " : "");
            if (!safeInventoryRptQuery.SHOW_ALL)
            {
                sql = sql + " HAVING (ISNULL(A.MIN_QTY,0) > 0 AND SUM(ISNULL(B.QTY,0) - ISNULL(B.PREALLOCATED_QTY,0)) < ISNULL(A.MIN_QTY,0)) OR (ISNULL(A.MAX_QTY,0) > 0 AND SUM(ISNULL(B.QTY,0) - ISNULL(B.PREALLOCATED_QTY,0)) > ISNULL(A.MAX_QTY,0))";
            }
            if (!safeInventoryRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, safeInventoryRptQuery.CurrentPage, safeInventoryRptQuery.PageSize, safeInventoryRptQuery.SortField, safeInventoryRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + safeInventoryRptQuery.SortField + " " + safeInventoryRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShelflifeRptInfo(List<string> sqlWhere, ShelflifeRptQueryEntity ShelflifeRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT * FROM ( ");
            sb.Append(" SELECT DISTINCT ISL.OWNER_ID, ISL.SKU_ID, SKU.DESCR AS SKU_DESCR,SKU.DESCR2 AS SKU_DESCR2,SKU.PACK_ID AS PACK_ID ,SKU.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_DESCR,SKU.SKU_GROUP,SKU.UDF2 AS SKU_UDF2,");
            sb.Append(" IL.LOT,IL.QTY,IL.UNIT_PRICE,IL.QTY * IL.UNIT_PRICE AS PRICE,U.UOM_DESCR,");
            sb.Append(" CASE WHEN DATEDIFF(D, GETDATE(), LA.EXPIRY_DATE) > 0 THEN '未过期' ELSE '已过期' END AS IS_EXPIRY, ");
            sb.Append(" DATEDIFF(D, GETDATE(), LA.EXPIRY_DATE) AS EXPIRY_DAY_COUNT, ");
            sb.Append(" DATEDIFF(D, LA.PRODUCE_DATE, LA.EXPIRY_DATE) AS SHELF_LIFE, ");
            sb.Append(" LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, LA.LOT_ATTR10, ");
            sb.Append(" LA.PRODUCE_DATE, LA.EXPIRY_DATE AS Expiry_Date, LA.VENDOR_ID, LA.EXTERNAL_LOT  ");
            sb.Append(" FROM @_@INV_SKU_LOC AS ISL ");
            sb.Append(" LEFT JOIN @_@INV_LOT AS IL ON ISL.OWNER_ID = IL.OWNER_ID AND ISL.SKU_ID = IL.SKU_ID ");
            sb.Append(" LEFT JOIN @_@SKU AS SKU ON ISL.OWNER_ID = SKU.OWNER_ID AND ISL.SKU_ID = SKU.SKU_ID ");
            sb.Append(" LEFT JOIN  @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID ");
            sb.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            sb.Append(" LEFT JOIN @_@SKU_CLASS SC ON SC.OWNER_ID = SKU.OWNER_ID AND SC.SKU_CLASS_ID = SKU.SKU_CLASS_ID ");
            sb.Append(" LEFT JOIN @_@LOT_ATTRIBUTE AS LA ON IL.LOT = LA.LOT ");
            sb.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            sb.Append(" WHERE (LA.EXPIRY_DATE IS NOT NULL) AND (LA.EXPIRY_DATE <> '') ");
            sb.Append(" ) TEMP INNER JOIN SYSADM.USER_OWNER UO ON TEMP.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID");
            string sql = sb.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!ShelflifeRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, ShelflifeRptQuery.CurrentPage, ShelflifeRptQuery.PageSize, ShelflifeRptQuery.SortField, ShelflifeRptQuery.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + ShelflifeRptQuery.SortField + " " + ShelflifeRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShipmentOrderDetailRptInfo(List<string> sqlWhere, ShipmentOrderDetailRptQueryEntity shipmentOrderDetailRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = " SELECT DISTINCT * FROM (  SELECT OD.ORDER_ID,OD.LINE_ID, O.EXTERN_ORDER_ID, O.REFERENCE_NUM, OD.OWNER_ID, PD.STATUS AS OD_STATUS,T.[TEXT] AS STATUS,   O.ORDER_DATE, T2.[TEXT] AS TYPE, O.REQUESTED_SHIP_DATE, OD.ACTUAL_SHIP_DATE, SKU.SKU_ID,   OD.UOM_ID, SKU.SKU_CLASS_ID, SKU.DESCR, OD.ORIGINAL_QTY, SUM(PD.QTY) AS SHIPPED_QTY,   OD.ORIGINAL_QTY - OD.SHIPPED_QTY AS OPEN_QTY, OD.PREALLOCATED_QTY, OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.ORIGINAL_QTY * SKU.GROSS_WGT AS TotalWeight,   OD.ORIGINAL_QTY * SKU.[CUBE] AS TotalCube, O.CONSIGNEE_ID, O.C_ADDRESS,   O.C_COMPANY, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03,   LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07,   LA.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, LA.LOT_ATTR10,PD.LOT,  LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.EXTERNAL_LOT,LA.RECEIPT_DATE, LA.VENDOR_ID, OD.REMARK,V.COMPANY,SC.DESCR AS SKU_CLASS_DESCR, LAC.DESCR  AS SKU_PROPERTY,OD.ORIGINAL_QTY * SKU.NET_WGT AS TOTAL_WGT,   OD.CREATED_BY, OD.CREATED_DATE,IL.UNIT_PRICE,SKU.SKU_STYLE,SKU.SKU_GROUP,U.UOM_DESCR,SKU.PACK_ID,WD.WAVE_ID   FROM  @_@ORDER_DETAIL OD  LEFT JOIN  @_@WAVE_DETAIL WD ON WD.ORDER_ID= OD.ORDER_ID   LEFT JOIN @_@PICK_DETAIL PD ON OD.ORDER_ID=PD.ORDER_ID AND OD.LINE_ID = PD.ORDER_LINE_ID  LEFT JOIN  @_@LOT_ATTRIBUTE LA ON PD.LOT =LA.LOT  LEFT  JOIN  @_@ORDERS O ON OD.ORDER_ID = O.ORDER_ID    LEFT JOIN @_@SKU SKU ON OD.OWNER_ID = SKU.OWNER_ID AND OD.SKU_ID = SKU.SKU_ID LEFT JOIN  @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID  LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1  LEFT JOIN @_@SKU_CLASS SC ON  SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SC.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@VENDOR V ON  OD.VENDOR_ID = V.VENDOR_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID           AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID           AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'  LEFT JOIN @_@INV_LOT IL ON PD.LOT = IL.LOT  GROUP BY OD.ORDER_ID,OD.LINE_ID, O.EXTERN_ORDER_ID, O.REFERENCE_NUM, OD.OWNER_ID, PD.STATUS ,T.[TEXT], O.ORDER_DATE, T2.[TEXT], O.REQUESTED_SHIP_DATE, OD.ACTUAL_SHIP_DATE, SKU.SKU_ID,  OD.UOM_ID, SKU.SKU_CLASS_ID, SKU.DESCR, OD.ORIGINAL_QTY, OD.ORIGINAL_QTY - OD.SHIPPED_QTY , OD.PREALLOCATED_QTY, OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.ORIGINAL_QTY * SKU.[CUBE], O.CONSIGNEE_ID, O.C_ADDRESS, O.C_COMPANY, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03,   LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07,  LA.LOT_ATTR08, LAC.DESCR , LA.LOT_ATTR10,PD.LOT,LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.EXTERNAL_LOT,LA.RECEIPT_DATE, LA.VENDOR_ID, OD.REMARK,V.COMPANY,SC.DESCR , LAC.DESCR  ,  OD.CREATED_BY, OD.CREATED_DATE ,SKU.GROSS_WGT,SKU.NET_WGT,IL.UNIT_PRICE,SKU.SKU_STYLE,SKU.SKU_GROUP,U.UOM_DESCR,SKU.PACK_ID,WD.WAVE_ID  ) TEMP WHERE (CONSIGNEE_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR CONSIGNEE_ID IS NULL OR CONSIGNEE_ID = '')";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!shipmentOrderDetailRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentOrderDetailRptQueryEntity.CurrentPage, shipmentOrderDetailRptQueryEntity.PageSize, shipmentOrderDetailRptQueryEntity.SortField, shipmentOrderDetailRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + shipmentOrderDetailRptQueryEntity.SortField + " " + shipmentOrderDetailRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShipmentOrderRptInfo(List<string> sqlWhere, ShipmentOrderRptQueryEntity shipmentOrderRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = " SELECT DISTINCT OD.ORDER_ID AS ORDER_ID,OD.LINE_ID AS LINE_ID ,O.EXTERNAL_ORDER_ID2 AS EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM AS REFERENCE_NUM, O.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, OD.OWNER_ID AS OWNER_ID, T.[TEXT] AS STATUS,   O.ORDER_DATE AS ORDER_DATE , T2.[TEXT] AS TYPE, O.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE , O.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, SKU.SKU_ID AS SKU_ID,   OD.UOM_ID AS UOM_ID , SKU.SKU_CLASS_ID AS SKU_CLASS_ID , SKU.DESCR AS DESCR, OD.ORIGINAL_QTY AS ORIGINAL_QTY, SUM(PD.QTY) AS SHIPPED_QTY, O.CARRIER_ID AS CARRIER_ID,O.CARRIER_NAME AS CARRIER_NAME,  OD.ORIGINAL_QTY - OD.SHIPPED_QTY AS OPEN_QTY, OD.PREALLOCATED_QTY AS PREALLOCATED_QTY, OD.ALLOCATED_QTY AS ALLOCATED_QTY, OD.PICKED_QTY AS PICKED_QTY, OD.ORIGINAL_QTY * SKU.GROSS_WGT AS TotalWeight,   OD.ORIGINAL_QTY * SKU.[CUBE] AS TotalCube, O.CONSIGNEE_ID AS CONSIGNEE_ID, O.C_ADDRESS AS C_ADDRESS,   O.C_COMPANY AS C_COMPANY ,LA.LOT_ATTR01 AS LOT_ATTR01, LA.LOT_ATTR02 AS LOT_ATTR02, LA.LOT_ATTR03 AS LOT_ATTR03,   LA.LOT_ATTR04 AS LOT_ATTR04, LA.LOT_ATTR05 AS LOT_ATTR05, LA.LOT_ATTR06 AS LOT_ATTR06, LA.LOT_ATTR07 AS LOT_ATTR07,   LA.LOT_ATTR08 AS LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, LA.LOT_ATTR10 AS LOT_ATTR10, LA.PRODUCE_DATE AS PRODUCE_DATE,   LA.EXPIRY_DATE AS EXPIRY_DATE, LA.EXTERNAL_LOT AS EXTERNAL_LOT, LA.VENDOR_ID AS VENDOR_ID, OD.REMARK AS REMARK,V.COMPANY AS COMPANY,SC.DESCR AS SKU_CLASS_DESCR, LAC.DESCR AS SKU_PROPERTY,OD.ORIGINAL_QTY * SKU.NET_WGT AS TOTAL_WGT,   OD.CREATED_BY AS CREATED_BY, OD.CREATED_DATE AS CREATED_DATE,SKU.PACK_ID,U.UOM_DESCR   FROM  @_@ORDER_DETAIL OD    LEFT JOIN  @_@PICK_DETAIL PD ON OD.ORDER_ID=PD.ORDER_ID AND OD.LINE_ID = PD.ORDER_LINE_ID  LEFT JOIN  @_@LOT_ATTRIBUTE LA ON PD.LOT =LA.LOT  LEFT JOIN  @_@ORDERS O ON OD.ORDER_ID = O.ORDER_ID   LEFT JOIN  @_@SKU SKU ON OD.OWNER_ID = SKU.OWNER_ID AND OD.SKU_ID = SKU.SKU_ID  LEFT JOIN  @_@PACK P ON P.PACK_ID = SKU.PACK_ID  LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1  LEFT JOIN @_@SKU_CLASS SC ON  SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID  LEFT JOIN @_@VENDOR V ON  OD.VENDOR_ID = V.VENDOR_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID           AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID           AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE O.STATUS = 'SHIPPED' AND (O.CONSIGNEE_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR O.CONSIGNEE_ID IS NULL OR O.CONSIGNEE_ID = '')";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            sql = sql + " GROUP BY OD.ORDER_ID,OD.LINE_ID,O.EXTERNAL_ORDER_ID2,O.REFERENCE_NUM , O.EXTERN_ORDER_ID , OD.OWNER_ID , T.[TEXT],  O.ORDER_DATE , T2.[TEXT], O.REQUESTED_SHIP_DATE , O.ACTUAL_SHIP_DATE, SKU.SKU_ID , OD.UOM_ID , SKU.SKU_CLASS_ID , SKU.DESCR , OD.ORIGINAL_QTY , O.CARRIER_ID ,O.CARRIER_NAME , OD.ORIGINAL_QTY , OD.SHIPPED_QTY , OD.PREALLOCATED_QTY , OD.ALLOCATED_QTY , OD.PICKED_QTY , OD.ORIGINAL_QTY , SKU.GROSS_WGT ,  OD.ORIGINAL_QTY , SKU.[CUBE] , O.CONSIGNEE_ID , O.C_ADDRESS , O.C_COMPANY ,LA.LOT_ATTR01, LA.LOT_ATTR02 , LA.LOT_ATTR03 ,   LA.LOT_ATTR04 , LA.LOT_ATTR05, LA.LOT_ATTR06 , LA.LOT_ATTR07 ,   LA.LOT_ATTR08 , LAC.DESCR , LA.LOT_ATTR10 , LA.PRODUCE_DATE , LA.EXPIRY_DATE , LA.EXTERNAL_LOT , LA.VENDOR_ID , OD.REMARK ,V.COMPANY ,SC.DESCR , LAC.DESCR,OD.ORIGINAL_QTY, SKU.NET_WGT, OD.CREATED_BY , OD.CREATED_DATE,SKU.PACK_ID,U.UOM_DESCR ";
            if (!shipmentOrderRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentOrderRptQuery.CurrentPage, shipmentOrderRptQuery.PageSize, shipmentOrderRptQuery.SortField, shipmentOrderRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + shipmentOrderRptQuery.SortField + " " + shipmentOrderRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShipmentOrderRptStatistics(List<string> sqlWhere, ShipmentOrderRptQueryEntity shipmentOrderRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = " SELECT DISTINCT OD.ORDER_ID AS ORDER_ID,OD.LINE_ID AS LINE_ID ,O.EXTERNAL_ORDER_ID2 AS EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM AS REFERENCE_NUM, O.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, OD.OWNER_ID AS OWNER_ID, T.[TEXT] AS STATUS,   O.ORDER_DATE AS ORDER_DATE , T2.[TEXT] AS TYPE, O.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE , O.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, SKU.SKU_ID AS SKU_ID,   OD.UOM_ID AS UOM_ID , SKU.SKU_CLASS_ID AS SKU_CLASS_ID , SKU.DESCR AS DESCR, OD.ORIGINAL_QTY AS ORIGINAL_QTY, OD.SHIPPED_QTY AS SHIPPED_QTY, O.CARRIER_ID AS CARRIER_ID,O.CARRIER_NAME AS CARRIER_NAME,  OD.ORIGINAL_QTY - OD.SHIPPED_QTY AS OPEN_QTY, OD.PREALLOCATED_QTY AS PREALLOCATED_QTY, OD.ALLOCATED_QTY AS ALLOCATED_QTY, OD.PICKED_QTY AS PICKED_QTY, OD.ORIGINAL_QTY * SKU.GROSS_WGT AS TotalWeight,   OD.ORIGINAL_QTY * SKU.[CUBE] AS TotalCube, O.CONSIGNEE_ID AS CONSIGNEE_ID, O.C_ADDRESS AS C_ADDRESS,   O.C_COMPANY AS C_COMPANY,OD.REMARK AS REMARK,V.COMPANY AS COMPANY,SC.DESCR AS SKU_CLASS_DESCR,OD.ORIGINAL_QTY * SKU.NET_WGT AS TOTAL_WGT,  OD.CREATED_BY AS CREATED_BY, OD.CREATED_DATE AS CREATED_DATE   FROM  @_@ORDER_DETAIL OD    LEFT  JOIN @_@ORDERS O ON OD.ORDER_ID = O.ORDER_ID LEFT JOIN   @_@SKU SKU ON OD.OWNER_ID = SKU.OWNER_ID AND OD.SKU_ID = SKU.SKU_ID LEFT JOIN @_@SKU_CLASS SC ON  SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID AND SKU.OWNER_ID = SC.OWNER_ID  LEFT JOIN @_@VENDOR V ON  OD.VENDOR_ID = V.VENDOR_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID           AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID           AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  WHERE O.STATUS IN ('PartShipped','Shipped') AND (O.CONSIGNEE_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR O.CONSIGNEE_ID IS NULL OR O.CONSIGNEE_ID = '')";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!shipmentOrderRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentOrderRptQuery.CurrentPage, shipmentOrderRptQuery.PageSize, shipmentOrderRptQuery.SortField, shipmentOrderRptQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + shipmentOrderRptQuery.SortField + " " + shipmentOrderRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<ShipmentOrderStatusRptInfo> GetShipmentOrderStatusRptInfo(List<string> sqlWhere, ShipmentOrderStatusRptQueryEntity shipmentOrderStatusRptQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSql = " SELECT DISTINCT T.TEXT AS STATUS, Os.ORDER_ID AS ORDER_ID, Os.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, Os.OWNER_ID AS OWNER_ID, T2.TEXT AS TYPE, Os.CONSIGNEE_ID AS CONSIGNEE_ID, Os.C_ADDRESS AS C_ADDRESS,  Os.ORDER_DATE AS ORDER_DATE, Os.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE, Os.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, Os.REMARK AS REMARK,Os.CARRIER_ID AS CARRIER_ID,Os.CARRIER_NAME AS CARRIER_NAME,Os.C_COMPANY AS C_COMPANY,Os.TYPE AS O_TYPE,Os.STATUS AS O_STATUS  FROM  @_@ORDERS AS Os LEFT OUTER JOIN  @_@CUSTOMER ON Os.CONSIGNEE_ID = @_@CUSTOMER.CUSTOMER_ID LEFT OUTER JOIN  @_@OWNER ON Os.OWNER_ID = @_@OWNER.OWNER_ID LEFT OUTER JOIN    @_@SYS_CODE_DETAIL AS SCD ON SCD.SYS_CODE = Os.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT OUTER JOIN  @_@SYS_CODE_DETAIL AS SCD2 ON SCD2.SYS_CODE = Os.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT OUTER JOIN  sysadm.TRANSLATION AS T ON 'OrderStatus.' + Os.STATUS = T.ITEM_ID AND T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND   T.LANG + '-' + T.COUNTRY = @LANGUAGE LEFT OUTER JOIN   sysadm.TRANSLATION AS T2 ON 'OrderType.' + Os.TYPE = T2.ITEM_ID AND T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND   T2.LANG + '-' + T2.COUNTRY = @LANGUAGE INNER JOIN  sysadm.USER_OWNER AS UO ON Os.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID INNER JOIN  @_@ORDER_DETAIL AS ORD ON Os.ORDER_ID = ORD.ORDER_ID INNER JOIN  @_@LOT_ATTRIBUTE AS LOTB ON UO.OWNER_ID = LOTB.OWNER_ID INNER JOIN  @_@SKU AS SKU ON UO.OWNER_ID = SKU.OWNER_ID AND UO.OWNER_ID = SKU.OWNER_ID AND   UO.OWNER_ID = SKU.OWNER_ID AND ORD.OWNER_ID = SKU.OWNER_ID AND   ORD.SKU_ID = SKU.SKU_ID AND ORD.OWNER_ID = SKU.OWNER_ID AND   ORD.SKU_ID = SKU.SKU_ID AND ORD.OWNER_ID = SKU.OWNER_ID AND   ORD.SKU_ID = SKU.SKU_ID AND LOTB.OWNER_ID = SKU.OWNER_ID AND   LOTB.SKU_ID = SKU.SKU_ID AND LOTB.OWNER_ID = SKU.OWNER_ID AND   LOTB.SKU_ID = SKU.SKU_ID AND LOTB.OWNER_ID = SKU.OWNER_ID AND   LOTB.SKU_ID = SKU.SKU_ID WHERE (Os.CONSIGNEE_ID IN (SELECT CUSTOMER_ID FROM SYSADM.USER_CUSTOMER WHERE [USER_ID] = @USER_ID) OR Os.CONSIGNEE_ID IS NULL OR Os.CONSIGNEE_ID = '') ";
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            if (!shipmentOrderStatusRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentOrderStatusRptQueryEntity.CurrentPage, shipmentOrderStatusRptQueryEntity.PageSize, shipmentOrderStatusRptQueryEntity.SortField, shipmentOrderStatusRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + shipmentOrderStatusRptQueryEntity.SortField + " " + shipmentOrderStatusRptQueryEntity.SortDirection;
            }
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            List<ShipmentOrderStatusRptInfo> shipmentOrderStatusRptInfos = new List<ShipmentOrderStatusRptInfo>();
            ShipmentOrderStatusRptInfo shipmentOrderStatusRptInfo = null;
            if ((ds != null) && (ds.Tables.Count > 0))
            {
                DateTime CS00003;
                if (ds.Tables.Count > 1)
                {
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            shipmentOrderStatusRptInfo = new ShipmentOrderStatusRptInfo(ds.Tables[2].Rows[i]["ORDER_ID"].ToString(), ds.Tables[2].Rows[i]["EXTERN_ORDER_ID"].ToString(), ds.Tables[2].Rows[i]["OWNER_ID"].ToString(), ds.Tables[2].Rows[i]["CONSIGNEE_ID"].ToString(), ds.Tables[2].Rows[i]["TYPE"].ToString(), ds.Tables[2].Rows[i]["STATUS"].ToString(), ds.Tables[2].Rows[i]["C_ADDRESS"].ToString(), (ds.Tables[2].Rows[i]["ORDER_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[2].Rows[i]["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[2].Rows[i]["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[2].Rows[i]["REMARK"].ToString(), ds.Tables[2].Rows[i]["CARRIER_ID"].ToString(), ds.Tables[2].Rows[i]["CARRIER_NAME"].ToString(), ds.Tables[2].Rows[i]["C_COMPANY"].ToString());
                            shipmentOrderStatusRptInfos.Add(shipmentOrderStatusRptInfo);
                        }
                    }
                    if ((shipmentOrderStatusRptInfos != null) && (shipmentOrderStatusRptInfos.Count > 0))
                    {
                        shipmentOrderStatusRptInfos[0].CURRENTPAGE = ds.Tables[1].Rows[0]["CurrentPage"].ToString();
                        shipmentOrderStatusRptInfos[0].TOTALPAGESCOUNT = ds.Tables[1].Rows[0]["TotalPagesCount"].ToString();
                        shipmentOrderStatusRptInfos[0].TOTALRECORDSCOUNT = ds.Tables[1].Rows[0]["TotalRecordsCount"].ToString();
                    }
                    return shipmentOrderStatusRptInfos;
                }
                if (ds.Tables[0].Rows.Count <= 0)
                {
                    return shipmentOrderStatusRptInfos;
                }
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    shipmentOrderStatusRptInfo = new ShipmentOrderStatusRptInfo(ds.Tables[0].Rows[i]["ORDER_ID"].ToString(), ds.Tables[0].Rows[i]["EXTERN_ORDER_ID"].ToString(), ds.Tables[0].Rows[i]["OWNER_ID"].ToString(), ds.Tables[0].Rows[i]["CONSIGNEE_ID"].ToString(), ds.Tables[0].Rows[i]["TYPE"].ToString(), ds.Tables[0].Rows[i]["STATUS"].ToString(), ds.Tables[0].Rows[i]["C_ADDRESS"].ToString(), (ds.Tables[0].Rows[i]["ORDER_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (ds.Tables[0].Rows[i]["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(ds.Tables[0].Rows[i]["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), ds.Tables[0].Rows[i]["REMARK"].ToString(), ds.Tables[0].Rows[i]["CARRIER_ID"].ToString(), ds.Tables[0].Rows[i]["CARRIER_NAME"].ToString(), ds.Tables[0].Rows[i]["C_COMPANY"].ToString());
                    shipmentOrderStatusRptInfos.Add(shipmentOrderStatusRptInfo);
                }
                shipmentOrderStatusRptInfos[0].CURRENTPAGE = "0";
                shipmentOrderStatusRptInfos[0].TOTALPAGESCOUNT = "0";
                shipmentOrderStatusRptInfos[0].TOTALRECORDSCOUNT = "0";
            }
            return shipmentOrderStatusRptInfos;
        }

        public DataSet GetShipmentStatByCustomerRptDataSet(List<string> sqlWhere, ShipmentStatByCustomerRptQueryEntity shipmentStatByCustomerRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT O.CONSIGNEE_ID AS CUSTOMER_ID, O.C_COMPANY AS CUSTOMER_NAME, ");
            strSql.Append(" COUNT(DISTINCT O.ORDER_ID) AS ORDERS_COUNT,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0)) QTY,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(S.GROSS_WGT,0)) AS GROSS_WGT,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(S.[CUBE],0)) AS [CUBE],");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(IL.UNIT_PRICE,0)) AS PRICE");
            strSql.Append(" FROM @_@ORDERS O LEFT JOIN @_@PICK_DETAIL PD ON PD.ORDER_ID = O.ORDER_ID AND PD.[STATUS] = 'Shipped'");
            strSql.Append("   LEFT JOIN @_@SKU S ON S.OWNER_ID = PD.OWNER_ID AND S.SKU_ID = PD.SKU_ID");
            strSql.Append("   LEFT JOIN @_@INV_LOT IL ON IL.OWNER_ID = PD.OWNER_ID AND IL.SKU_ID = PD.SKU_ID AND IL.LOT = PD.LOT");
            strSql.Append("   INNER JOIN sysadm.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            strSql.Append(" WHERE O.[STATUS] IN ('Shipped','PartShipped') ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSql.Append(" AND " + sqlWhere[i].ToString());
            }
            strSql.Append(" AND EXISTS(SELECT 1 FROM @_@ORDER_DETAIL WHERE ORDER_ID = O.ORDER_ID) ");
            strSql.Append(" GROUP BY O.CONSIGNEE_ID, O.C_COMPANY");
            string sql = string.Empty;
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            if (!shipmentStatByCustomerRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentStatByCustomerRptQueryEntity.CurrentPage, shipmentStatByCustomerRptQueryEntity.PageSize, shipmentStatByCustomerRptQueryEntity.SortField, shipmentStatByCustomerRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + shipmentStatByCustomerRptQueryEntity.SortField + " " + shipmentStatByCustomerRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShipmentStatByOrderRptDataSet(List<string> sqlWhere, ShipmentStatByOrderRptQueryEntity shipmentStatByOrderRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT A.ORDER_ID, A.ORDER_STATUS, A.ORDER_TYPE, A.REFERENCE_NUM, A.EXTERN_ORDER_ID, A.CUSTOMER_ID,A.CUSTOMER_NAME, A.CUSTOMER_ADDRESS,");
            strSql.Append(" A.CARRIER_ID,A.CARRIER_NAME,A.CREATED_DATE,A.ORDER_DATE,A.DELIVERY_DATE,A.REQUESTED_SHIP_DATE,A.ACTUAL_SHIP_DATE,A.REMARK,");
            strSql.Append(" A.QTY,A.GROSS_WGT,A.[CUBE],A.PRICE,SUM(OD.ORIGINAL_QTY) AS ORIGINAL_QTY ");
            strSql.Append(" FROM (");
            strSql.Append(" SELECT O.ORDER_ID, T.[TEXT] AS ORDER_STATUS, T2.[TEXT] AS ORDER_TYPE, O.REFERENCE_NUM, O.EXTERN_ORDER_ID,");
            strSql.Append(" O.CONSIGNEE_ID AS CUSTOMER_ID, O.C_COMPANY AS CUSTOMER_NAME, O.C_ADDRESS AS CUSTOMER_ADDRESS,");
            strSql.Append(" O.CARRIER_ID, O.CARRIER_NAME, O.CREATED_DATE, O.ORDER_DATE, O.DELIVERY_DATE, O.REQUESTED_SHIP_DATE, O.ACTUAL_SHIP_DATE, O.REMARK,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0)) QTY,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(S.GROSS_WGT,0)) AS GROSS_WGT,");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(S.[CUBE],0)) AS [CUBE],");
            strSql.Append(" SUM(ISNULL(PD.QTY, 0) * ISNULL(IL.UNIT_PRICE,0)) AS PRICE");
            strSql.Append(" FROM @_@ORDERS O INNER JOIN @_@PICK_DETAIL PD ON PD.ORDER_ID = O.ORDER_ID AND PD.[STATUS] = 'Shipped'");
            strSql.Append("   LEFT JOIN @_@SKU S ON S.OWNER_ID = PD.OWNER_ID AND S.SKU_ID = PD.SKU_ID");
            strSql.Append("   LEFT JOIN @_@INV_LOT IL ON IL.OWNER_ID = PD.OWNER_ID AND IL.SKU_ID = PD.SKU_ID AND IL.LOT = PD.LOT");
            strSql.Append("   LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.' + O.[STATUS] = T.ITEM_ID AND T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG + '-' + T.COUNTRY = @LANGUAGE");
            strSql.Append("   LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+ O.TYPE = T2.ITEM_ID AND T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG + '-' + T2.COUNTRY = @LANGUAGE");
            strSql.Append("   INNER JOIN sysadm.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ");
            strSql.Append(" WHERE O.[STATUS] IN ('Shipped','PartShipped') ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSql.Append(" AND " + sqlWhere[i].ToString());
            }
            strSql.Append(" GROUP BY O.ORDER_ID, T.[TEXT], T2.[TEXT], O.REFERENCE_NUM, O.EXTERN_ORDER_ID,");
            strSql.Append(" O.CONSIGNEE_ID, O.C_COMPANY, O.C_ADDRESS,");
            strSql.Append(" O.CARRIER_ID, O.CARRIER_NAME, O.CREATED_DATE, O.ORDER_DATE, O.DELIVERY_DATE, O.REQUESTED_SHIP_DATE, O.ACTUAL_SHIP_DATE, O.REMARK");
            strSql.Append(" ) A INNER JOIN @_@ORDER_DETAIL OD ON A.ORDER_ID = OD.ORDER_ID");
            strSql.Append(" GROUP BY A.ORDER_ID, A.ORDER_STATUS, A.ORDER_TYPE, A.REFERENCE_NUM, A.EXTERN_ORDER_ID, A.CUSTOMER_ID,A.CUSTOMER_NAME, A.CUSTOMER_ADDRESS,");
            strSql.Append(" A.CARRIER_ID,A.CARRIER_NAME,A.CREATED_DATE,A.ORDER_DATE,A.DELIVERY_DATE,A.REQUESTED_SHIP_DATE,A.ACTUAL_SHIP_DATE,A.REMARK,");
            strSql.Append(" A.QTY,A.GROSS_WGT,A.[CUBE],A.PRICE");
            string sql = string.Empty;
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            if (!shipmentStatByOrderRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentStatByOrderRptQueryEntity.CurrentPage, shipmentStatByOrderRptQueryEntity.PageSize, shipmentStatByOrderRptQueryEntity.SortField, shipmentStatByOrderRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + shipmentStatByOrderRptQueryEntity.SortField + " " + shipmentStatByOrderRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShipmentStatSplitCustomerRptDataSet(List<string> sqlWhere, ShipmentStatSplitCustomerRptQueryEntity shipmentStatSplitCustomerRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            string strTableWhere = string.Empty;
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.OWNER_ID))
            {
                strTableWhere = " AND O.OWNER_ID " + shipmentStatSplitCustomerRptQueryEntity.OWNER_ID;
            }
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.SKU_ID))
            {
                strTableWhere = strTableWhere + " AND PD.SKU_ID " + shipmentStatSplitCustomerRptQueryEntity.SKU_ID;
            }
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.ORDER_TYPE))
            {
                strTableWhere = strTableWhere + " AND O.TYPE " + shipmentStatSplitCustomerRptQueryEntity.ORDER_TYPE;
            }
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.StartTime))
            {
                strTableWhere = strTableWhere + " AND OD.ACTUAL_SHIP_DATE >= '" + shipmentStatSplitCustomerRptQueryEntity.StartTime + "'";
            }
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.EndTime))
            {
                strTableWhere = strTableWhere + " AND OD.ACTUAL_SHIP_DATE <= '" + shipmentStatSplitCustomerRptQueryEntity.EndTime + "'";
            }
            if (!string.IsNullOrEmpty(shipmentStatSplitCustomerRptQueryEntity.CUSTOMER_ID))
            {
                strTableWhere = strTableWhere + " AND O.CONSIGNEE_ID " + shipmentStatSplitCustomerRptQueryEntity.CUSTOMER_ID;
            }
            strWhere = strTableWhere;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strWhere = strWhere + " AND " + sqlWhere[i].ToString();
            }
            StringBuilder strCreateTable = new StringBuilder();
            StringBuilder strSql = new StringBuilder();
            if (shipmentStatSplitCustomerRptQueryEntity.IS_LOT == "Y")
            {
                strCreateTable.Append(" DECLARE @SQL VARCHAR(8000)  IF OBJECT_ID('TEMPDB.DBO.##TABLE') IS NOT NULL BEGIN  DROP TABLE ##TABLE END ");
                strCreateTable.Append(" SET @SQL = 'SELECT O.OWNER_ID as TABLE_OWNER_ID ,OD.SKU_ID AS TABLE_SKU_ID,PD.LOT AS TABLE_LOT '");
                strCreateTable.Append(" SELECT @SQL = @SQL + ' , SUM(CASE C_COMPANY WHEN ''' + C_COMPANY + ''' THEN PD.QTY ELSE 0 END) [' + C_COMPANY + ']' ");
                strCreateTable.Append(" FROM (SELECT DISTINCT C_COMPANY FROM  @_@ORDERS O INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID WHERE C_COMPANY IS NOT NULL AND STATUS IN ('PARTSHIPPED','SHIPPED')) AS A ");
                strCreateTable.AppendFormat(" SET @SQL = @SQL + ' INTO ##TABLE FROM  @_@ORDERS O INNER JOIN @_@ORDER_DETAIL OD  ON O.ORDER_ID =OD.ORDER_ID INNER JOIN  @_@PICK_DETAIL  PD ON PD.ORDER_ID =OD.ORDER_ID AND PD.ORDER_LINE_ID = OD.LINE_ID   WHERE O.STATUS IN (''PARTSHIPPED'',''SHIPPED'') {0} GROUP BY O.OWNER_ID,OD.SKU_ID,PD.LOT'", strTableWhere.Replace("'", "''"));
                strCreateTable.Append(" EXEC(@SQL)   ");
                if (shipmentStatSplitCustomerRptQueryEntity.SHOW_DATE == "N")
                {
                    strSql.Append(" SELECT  A.*,T.* FROM (");
                    strSql.Append(" SELECT  PD.OWNER_ID AS OWNER_ID ,PD.SKU_ID AS SKU_ID,SUM(PD.QTY) AS QTY,PD.PACK_ID,S.DESCR AS SKU_DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2 AS SKU_UDF2,S.DESCR2 AS SKU_DESCR2, ");
                    strSql.Append(" LA.PRODUCE_DATE,LA.EXPIRY_DATE, (S.GROSS_WGT * SUM(PD.QTY)) AS TOTAL_WGT, (S.[CUBE] * SUM(PD.QTY)) AS TOTAL_CUBE,PD.LOT,IL.UNIT_PRICE AS UNIT_PRICE ,U.UOM_DESCR ");
                    strSql.Append(" FROM @_@PICK_DETAIL PD");
                    strSql.Append(" LEFT JOIN  @_@ORDER_DETAIL OD ON PD.ORDER_ID=OD.ORDER_ID  AND PD.ORDER_LINE_ID = OD.LINE_ID ");
                    strSql.Append(" LEFT JOIN @_@SKU  S ON PD.OWNER_ID =S.OWNER_ID AND PD.SKU_ID = S.SKU_ID");
                    strSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
                    strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
                    strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT =LA.LOT");
                    strSql.Append(" LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID");
                    strSql.Append("  LEFT JOIN @_@INV_LOT IL ON PD.LOT = IL.LOT ");
                    strSql.AppendFormat(" WHERE PD.STATUS='Shipped' {0} ", strWhere);
                    strSql.Append(" GROUP BY PD.OWNER_ID,PD.SKU_ID,PD.PACK_ID,S.DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2, LA.PRODUCE_DATE,LA.EXPIRY_DATE,S.GROSS_WGT,S.[CUBE],S.DESCR2,PD.LOT,IL.UNIT_PRICE,U.UOM_DESCR  ");
                    strSql.Append("  ) A ");
                    strSql.Append("  LEFT JOIN ##Table T ON A.OWNER_ID = T.TABLE_OWNER_ID AND A.SKU_ID =T.TABLE_SKU_ID AND A.LOT = T.TABLE_LOT ");
                }
                else
                {
                    strSql.Append(" SELECT  A.*,T.* FROM (");
                    strSql.Append(" SELECT  PD.OWNER_ID AS OWNER_ID ,PD.SKU_ID AS SKU_ID,SUM(PD.QTY) AS QTY,PD.PACK_ID,Convert(varchar,O.ACTUAL_SHIP_DATE,102) AS ACTUAL_SHIP_DATE,S.DESCR AS SKU_DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2 AS SKU_UDF2,S.DESCR2 AS SKU_DESCR2, ");
                    strSql.Append(" LA.PRODUCE_DATE,LA.EXPIRY_DATE, (S.GROSS_WGT * SUM(PD.QTY)) AS TOTAL_WGT, (S.[CUBE] * SUM(PD.QTY)) AS TOTAL_CUBE,PD.LOT,IL.UNIT_PRICE,U.UOM_DESCR  ");
                    strSql.Append(" FROM @_@PICK_DETAIL PD");
                    strSql.Append(" LEFT JOIN  @_@ORDER_DETAIL OD ON PD.ORDER_ID=OD.ORDER_ID  AND PD.ORDER_LINE_ID = OD.LINE_ID ");
                    strSql.Append(" LEFT JOIN @_@SKU  S ON PD.OWNER_ID =S.OWNER_ID AND PD.SKU_ID = S.SKU_ID");
                    strSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
                    strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
                    strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT =LA.LOT");
                    strSql.Append(" LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID");
                    strSql.Append("  LEFT JOIN @_@INV_LOT IL ON PD.LOT = IL.LOT ");
                    strSql.AppendFormat(" WHERE PD.STATUS='Shipped' {0} ", strWhere);
                    strSql.Append(" GROUP BY PD.OWNER_ID,PD.SKU_ID,PD.PACK_ID,S.DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2, LA.PRODUCE_DATE,LA.EXPIRY_DATE,S.GROSS_WGT,S.[CUBE],S.DESCR2,PD.LOT,Convert(varchar,O.ACTUAL_SHIP_DATE,102),IL.UNIT_PRICE,U.UOM_DESCR  ");
                    strSql.Append("  ) A ");
                    strSql.Append("  LEFT JOIN ##Table T ON A.OWNER_ID = T.TABLE_OWNER_ID AND A.SKU_ID =T.TABLE_SKU_ID AND A.LOT = T.TABLE_LOT ");
                }
            }
            else
            {
                strCreateTable.Append(" DECLARE @SQL VARCHAR(8000)  IF OBJECT_ID('TEMPDB.DBO.##TABLE') IS NOT NULL BEGIN  DROP TABLE ##TABLE END ");
                strCreateTable.Append(" SET @SQL = 'SELECT O.OWNER_ID as TABLE_OWNER_ID ,OD.SKU_ID AS TABLE_SKU_ID '");
                strCreateTable.Append(" SELECT @SQL = @SQL + ' , SUM(CASE C_COMPANY WHEN ''' + C_COMPANY + ''' THEN PD.QTY ELSE 0 END) [' + C_COMPANY + ']' ");
                strCreateTable.Append(" FROM (SELECT DISTINCT C_COMPANY FROM  @_@ORDERS O INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID WHERE C_COMPANY IS NOT NULL AND STATUS IN ('PARTSHIPPED','SHIPPED')) AS A ");
                strCreateTable.AppendFormat(" SET @SQL = @SQL + ' INTO ##TABLE FROM  @_@ORDERS O INNER JOIN @_@ORDER_DETAIL OD  ON O.ORDER_ID =OD.ORDER_ID INNER JOIN  @_@PICK_DETAIL  PD ON PD.ORDER_ID =OD.ORDER_ID AND PD.ORDER_LINE_ID = OD.LINE_ID  WHERE O.STATUS IN (''PARTSHIPPED'',''SHIPPED'') {0} GROUP BY O.OWNER_ID,OD.SKU_ID'", strTableWhere.Replace("'", "''"));
                strCreateTable.Append(" EXEC(@SQL)   ");
                if (shipmentStatSplitCustomerRptQueryEntity.SHOW_DATE == "N")
                {
                    strSql.Append(" SELECT  A.*,T.* FROM (");
                    strSql.Append(" SELECT  PD.OWNER_ID AS OWNER_ID,PD.SKU_ID AS SKU_ID,SUM(PD.QTY) AS QTY,PD.PACK_ID,S.DESCR AS SKU_DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2 AS SKU_UDF2,S.DESCR2 AS SKU_DESCR2, ");
                    strSql.Append(" (S.GROSS_WGT *SUM(PD.QTY)) AS TOTAL_WGT, (S.[CUBE] * SUM(PD.QTY)) AS TOTAL_CUBE,U.UOM_DESCR ");
                    strSql.Append(" FROM @_@PICK_DETAIL PD");
                    strSql.Append(" LEFT JOIN  @_@ORDER_DETAIL OD ON PD.ORDER_ID=OD.ORDER_ID  AND PD.ORDER_LINE_ID = OD.LINE_ID ");
                    strSql.Append(" LEFT JOIN @_@SKU  S ON PD.OWNER_ID =S.OWNER_ID AND PD.SKU_ID = S.SKU_ID");
                    strSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
                    strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
                    strSql.Append(" LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID");
                    strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT");
                    strSql.AppendFormat(" WHERE PD.STATUS='Shipped' {0} ", strTableWhere);
                    strSql.Append(" GROUP BY PD.OWNER_ID,PD.SKU_ID,PD.PACK_ID,S.DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2,S.GROSS_WGT,S.[CUBE],S.DESCR2,U.UOM_DESCR  ");
                    strSql.Append("  ) A ");
                    strSql.Append(" LEFT JOIN ##Table T ON A.OWNER_ID = T.TABLE_OWNER_ID AND A.SKU_ID =T.TABLE_SKU_ID  ");
                }
                else
                {
                    strSql.Append(" SELECT  A.*,T.* FROM (");
                    strSql.Append(" SELECT  PD.OWNER_ID AS OWNER_ID,PD.SKU_ID AS SKU_ID,SUM(PD.QTY) AS QTY,PD.PACK_ID,Convert(varchar,O.ACTUAL_SHIP_DATE,102) AS ACTUAL_SHIP_DATE ,S.DESCR AS SKU_DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2 AS SKU_UDF2,S.DESCR2 AS SKU_DESCR2, ");
                    strSql.Append(" (S.GROSS_WGT *SUM(PD.QTY)) AS TOTAL_WGT, (S.[CUBE] * SUM(PD.QTY)) AS TOTAL_CUBE ,U.UOM_DESCR");
                    strSql.Append(" FROM @_@PICK_DETAIL PD");
                    strSql.Append(" LEFT JOIN  @_@ORDER_DETAIL OD ON PD.ORDER_ID=OD.ORDER_ID  AND PD.ORDER_LINE_ID = OD.LINE_ID ");
                    strSql.Append(" LEFT JOIN @_@SKU  S ON PD.OWNER_ID =S.OWNER_ID AND PD.SKU_ID = S.SKU_ID");
                    strSql.Append(" LEFT JOIN  @_@PACK PACK ON S.PACK_ID = PACK.PACK_ID ");
                    strSql.Append(" LEFT JOIN  @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
                    strSql.Append(" LEFT JOIN @_@ORDERS O ON PD.ORDER_ID = O.ORDER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
                    strSql.Append("  INNER JOIN SYSADM.USER_CUSTOMER UC ON O.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.USER_ID = @USER_ID");
                    strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT");
                    strSql.AppendFormat(" WHERE PD.STATUS='Shipped' {0} ", strTableWhere);
                    strSql.Append(" GROUP BY PD.OWNER_ID,PD.SKU_ID,PD.PACK_ID,S.DESCR,S.SKU_CLASS_ID,S.SKU_GROUP,S.UDF2,S.GROSS_WGT,S.[CUBE],S.DESCR2,Convert(varchar,O.ACTUAL_SHIP_DATE,102),U.UOM_DESCR ");
                    strSql.Append("  ) A ");
                    strSql.Append(" LEFT JOIN ##Table T ON A.OWNER_ID = T.TABLE_OWNER_ID AND A.SKU_ID =T.TABLE_SKU_ID  ");
                }
            }
            string sql = string.Empty;
            string sqlCreateTable = string.Empty;
            sqlCreateTable = strCreateTable.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            if (!shipmentStatSplitCustomerRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, shipmentStatSplitCustomerRptQueryEntity.CurrentPage, shipmentStatSplitCustomerRptQueryEntity.PageSize, shipmentStatSplitCustomerRptQueryEntity.SortField, shipmentStatSplitCustomerRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + shipmentStatSplitCustomerRptQueryEntity.SortField + " " + shipmentStatSplitCustomerRptQueryEntity.SortDirection;
            }
            sql = sql + "   IF OBJECT_ID('TEMPDB.DBO.##TABLE') IS NOT NULL BEGIN  DROP TABLE ##TABLE END ";
            return DBHelper.ExecuteDataSet(CommandType.Text, sqlCreateTable + sql);
        }

        public DataSet GetStockRptInfo(List<string> sqlWhere, StockRptQueryEntity stockRptQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT DISTINCT * FROM (  ");
            strSql.Append(" SELECT DISTINCT ILLL.LOT,ILLL.LOC,ILLL.LPN_ID,SKU.OWNER_ID,ILLL.SKU_ID, SKU.DESCR, SKU.SKU_CLASS_ID,");
            strSql.Append(" ILLL.ALLOCATED_QTY,  ILLL.PICKED_QTY,PACK.PACK_UOM1, ILLL.QTY * SKU.GROSS_WGT AS GROSS_WGT, ");
            strSql.Append(" ILLL.QTY * SKU.[CUBE] AS CUBE,ILLL.STATUS, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04,  LA.LOT_ATTR05, LA.LOT_ATTR06,");
            strSql.Append(" LA.LOT_ATTR07, LA.LOT_ATTR08,LAC.DESCR AS LOT_ATTR09, LA.LOT_ATTR10, LA.PRODUCE_DATE, LA.EXPIRY_DATE, ");
            strSql.Append(" LA.EXTERNAL_LOT,LA.RECEIPT_DATE, ILLL.QTY,PO.CREATED_DATE AS PO_DATE, ");
            strSql.Append(" ILLL.QTY - ILLL.ALLOCATED_QTY - ILLL.PICKED_QTY AS CANUSE_QTY, LA.VENDOR_ID ,IL.UNIT_PRICE,SKU.SKU_STYLE,U.UOM_DESCR,SKU.PACK_ID ");
            strSql.Append(" FROM @_@INV_LOT_LOC_LPN ILLL ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON ILLL.LOT = LA.LOT ");
            strSql.Append(" LEFT JOIN @_@SKU SKU ON LA.OWNER_ID = SKU.OWNER_ID AND LA.SKU_ID = SKU.SKU_ID ");
            strSql.Append(" LEFT JOIN @_@RECEIPT R ON LA.LOT_ATTR10 = R.RECEIPT_ID ");
            strSql.Append(" LEFT JOIN @_@PURCHASE_ORDER PO ON R.PO_ID=PO.PO_ID");
            strSql.Append(" LEFT JOIN @_@PACK PACK ON SKU.PACK_ID = PACK.PACK_ID ");
            strSql.Append(" LEFT JOIN @_@UOM U ON U.UOM_ID = PACK.PACK_UOM1 ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' ");
            strSql.Append(" LEFT JOIN @_@INV_LOT IL ON ILLL.LOT = IL.LOT");
            strSql.Append(" WHERE ILLL.OWNER_ID IN (SELECT OWNER_ID FROM SYSADM.USER_OWNER WHERE USER_ID = @USER_ID) ");
            strSql.Append(" AND (LA.VENDOR_ID IN (SELECT VENDOR_ID FROM SYSADM.USER_VENDOR WHERE USER_ID = @USER_ID) OR LA.VENDOR_ID = '' OR LA.VENDOR_ID IS NULL) ");
            if (stockRptQuery.ISHISTORY.ToUpper() == "N")
            {
                strSql.Append(" AND ILLL.QTY > 0 ");
            }
            strSql.Append(" ) TEMP");
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!stockRptQuery.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, stockRptQuery.CurrentPage, stockRptQuery.PageSize, stockRptQuery.SortField, stockRptQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + stockRptQuery.SortField + " " + stockRptQuery.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetVendorReturnDetailRptDataSet(List<string> sqlWhere, VendorReturnDetailRptQueryEntity vendorReturnDetailRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT O.CONSIGNEE_ID AS VENDOR_ID,O.C_COMPANY AS VENDOR_NAME,O.ORDER_DATE AS ORDER_DATE,O.OWNER_ID AS OWNER_ID,PD.SKU_ID AS SKU_ID,S.DESCR AS SKU_DESCR,S.PACK_ID AS PACK_ID,U.UOM_DESCR,SUM(PD.QTY) AS QTY, ");
            strSql.Append("  IL.UNIT_PRICE AS UNIT_PRICE, IL.UNIT_PRICE * SUM(PD.QTY) AS TOTAL_PRICE,  O.REMARK AS REMARK,PD.ORDER_ID AS ORDER_ID,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.EXTERNAL_LOT AS EXTERNAL_LOT,S.SKU_CLASS_ID AS SKU_CLASS_ID,S.SKU_GROUP AS SKU_GROUP,S.UDF2 AS SKU_UDF2,S.DESCR2 AS SKU_DESCR2, ");
            strSql.Append("  LA.LOT AS LOT,LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03,LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05,LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,LA.LOT_ATTR10 AS LOT_ATTR10 ");
            strSql.Append("   FROM @_@PICK_DETAIL  PD  INNER JOIN  @_@ORDERS O   ON O.ORDER_ID = PD.ORDER_ID   INNER JOIN @_@SKU S  ON   PD.OWNER_ID =S.OWNER_ID AND PD.SKU_ID = S.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  LEFT JOIN @_@UOM U ON U.UOM_ID = P.PACK_UOM1  LEFT JOIN  @_@INV_LOT IL ON IL.LOT = PD.LOT  LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT  LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            strSql.Append("  INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
            strSql.Append("  WHERE  O.TYPE ='RETURN ' AND PD.STATUS ='Shipped'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSql.Append(" AND " + sqlWhere[i].ToString());
            }
            strSql.Append(" GROUP BY O.CONSIGNEE_ID,O.C_COMPANY ,O.ORDER_DATE,O.OWNER_ID,PD.SKU_ID,S.DESCR,S.PACK_ID,IL.UNIT_PRICE,O.REMARK,PD.ORDER_ID,IL.UNIT_PRICE,LA.LOT,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LAC.DESCR ,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.EXTERNAL_LOT,S.SKU_GROUP,S.UDF2 ,S.DESCR2,S.SKU_CLASS_ID,U.UOM_DESCR ");
            string sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            if (!vendorReturnDetailRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, vendorReturnDetailRptQueryEntity.CurrentPage, vendorReturnDetailRptQueryEntity.PageSize, vendorReturnDetailRptQueryEntity.SortField, vendorReturnDetailRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + vendorReturnDetailRptQueryEntity.SortField + " " + vendorReturnDetailRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetWarehouseTransferRptDateSet(List<string> sqlWhere, WarehouseTransferRptQueryEntity warehouseTransferRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            string StrGroupby = string.Empty;
            StringBuilder strSql = new StringBuilder();
            if (warehouseTransferRptQueryEntity.ShowLot == "N")
            {
                if (warehouseTransferRptQueryEntity.ShowDate == "Y")
                {
                    strSql.Append(" SELECT I.INTRANSIT_ID, I.ORDER_ID,I.RECEIPT_ID,I.ACTUAL_SHIP_DATE, SRCDC.WH_DB_DESC AS SRC_WH_DB_DESC ,DESTCDC.WH_DB_DESC AS DEST_WH_DB_DESC,I.OWNER_ID AS OWNER_ID,ID.SKU_ID AS SKU_ID, S.DESCR AS DESCR,S.PACK_ID AS PACK_ID,S.SKU_GROUP AS SKU_GROUP,S.SKU_CLASS_ID AS SKU_CLASS_ID,S.DESCR2 AS DESCR2,S.UDF2 AS UDF2,SUM(PD.QTY) AS QTY FROM  @_@INTRANSIT_DETAIL  ID");
                    strSql.Append(" LEFT JOIN @_@INTRANSIT I ON ID.INTRANSIT_ID=I.INTRANSIT_ID");
                    strSql.Append(" LEFT JOIN @_@SKU S ON S.SKU_ID=ID.SKU_ID  AND S.OWNER_ID=ID.OWNER_ID");
                    strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG SRCDC ON I.SRC_WH_DB_ID=SRCDC.WH_DB_ID");
                    strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG DESTCDC ON I.DEST_WH_DB_ID=DESTCDC.WH_DB_ID ");
                    strSql.Append(" LEFT JOIN @_@PICK_DETAIL PD ON I.ORDER_ID =PD.ORDER_ID AND ID.ORDER_LINE_ID=PD.ORDER_LINE_ID");
                    strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT  WHERE PD.STATUS ='Shipped' ");
                    StrGroupby = " GROUP BY I.INTRANSIT_ID, I.ORDER_ID,I.RECEIPT_ID,I.ACTUAL_SHIP_DATE, SRCDC.WH_DB_DESC ,DESTCDC.WH_DB_DESC ,I.OWNER_ID,S.SKU_CLASS_ID ,ID.SKU_ID, S.DESCR ,S.PACK_ID ,S.SKU_GROUP ,S.DESCR2 ,S.UDF2";
                }
                else
                {
                    strSql.Append(" SELECT SRCDC.WH_DB_DESC AS SRC_WH_DB_DESC ,DESTCDC.WH_DB_DESC AS DEST_WH_DB_DESC,I.OWNER_ID AS OWNER_ID,ID.SKU_ID AS SKU_ID, S.DESCR AS DESCR,S.SKU_CLASS_ID AS SKU_CLASS_ID,S.PACK_ID AS PACK_ID,S.SKU_GROUP AS SKU_GROUP,S.DESCR2 AS DESCR2,S.UDF2 AS UDF2,SUM(PD.QTY) AS QTY FROM  @_@INTRANSIT_DETAIL  ID");
                    strSql.Append(" LEFT JOIN @_@INTRANSIT I ON ID.INTRANSIT_ID=I.INTRANSIT_ID");
                    strSql.Append(" LEFT JOIN @_@SKU S ON S.SKU_ID=ID.SKU_ID  AND S.OWNER_ID=ID.OWNER_ID");
                    strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG SRCDC ON I.SRC_WH_DB_ID=SRCDC.WH_DB_ID");
                    strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG DESTCDC ON I.DEST_WH_DB_ID=DESTCDC.WH_DB_ID  ");
                    strSql.Append(" LEFT JOIN @_@PICK_DETAIL PD ON I.ORDER_ID =PD.ORDER_ID AND ID.ORDER_LINE_ID=PD.ORDER_LINE_ID");
                    strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT   WHERE PD.STATUS ='Shipped'");
                    StrGroupby = " GROUP BY SRCDC.WH_DB_DESC  ,DESTCDC.WH_DB_DESC ,I.OWNER_ID ,ID.SKU_ID, S.DESCR ,S.PACK_ID,S.SKU_CLASS_ID ,S.SKU_GROUP ,S.DESCR2 ,S.UDF2";
                }
            }
            else if (warehouseTransferRptQueryEntity.ShowDate == "Y")
            {
                strSql.Append(" SELECT I.INTRANSIT_ID, I.ORDER_ID,I.RECEIPT_ID,I.ACTUAL_SHIP_DATE,  SRCDC.WH_DB_DESC AS SRC_WH_DB_DESC ,DESTCDC.WH_DB_DESC AS DEST_WH_DB_DESC,I.OWNER_ID AS OWNER_ID,ID.SKU_ID AS SKU_ID, S.DESCR AS DESCR,S.SKU_CLASS_ID AS SKU_CLASS_ID,S.PACK_ID AS PACK_ID,S.SKU_GROUP AS SKU_GROUP,S.DESCR2 AS DESCR2,S.UDF2 AS UDF2, SUM(PD.QTY) AS QTY, ");
                strSql.Append(" PD.LOT AS LOT,LA.EXTERNAL_LOT AS EXTERNAL_LOT,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.VENDOR_ID AS VENDOR_ID,LA.RECEIPT_DATE AS RECEIPT_DATE,LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03,LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05,LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,LA.LOT_ATTR10 AS LOT_ATTR10 ");
                strSql.Append(" FROM  @_@INTRANSIT_DETAIL  ID");
                strSql.Append(" LEFT JOIN @_@INTRANSIT I ON ID.INTRANSIT_ID=I.INTRANSIT_ID");
                strSql.Append(" LEFT JOIN @_@SKU S ON S.SKU_ID=ID.SKU_ID AND S.OWNER_ID=ID.OWNER_ID");
                strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG SRCDC ON I.SRC_WH_DB_ID=SRCDC.WH_DB_ID");
                strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG DESTCDC ON I.DEST_WH_DB_ID=DESTCDC.WH_DB_ID ");
                strSql.Append(" LEFT JOIN @_@PICK_DETAIL PD ON I.ORDER_ID =PD.ORDER_ID AND ID.ORDER_LINE_ID=PD.ORDER_LINE_ID");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT ");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE PD.STATUS ='Shipped' ");
                StrGroupby = "  GROUP BY I.INTRANSIT_ID, I.ORDER_ID,I.RECEIPT_ID,I.ACTUAL_SHIP_DATE, SRCDC.WH_DB_DESC ,S.SKU_CLASS_ID ,DESTCDC.WH_DB_DESC ,I.OWNER_ID ,ID.SKU_ID, S.DESCR ,S.PACK_ID ,S.SKU_GROUP ,S.DESCR2 ,S.SKU_CLASS_ID ,S.UDF2,PD.LOT, LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LA.RECEIPT_DATE,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LAC.DESCR,LA.LOT_ATTR10";
            }
            else
            {
                strSql.Append(" SELECT   SRCDC.WH_DB_DESC AS SRC_WH_DB_DESC ,DESTCDC.WH_DB_DESC AS DEST_WH_DB_DESC,I.OWNER_ID AS OWNER_ID,ID.SKU_ID AS SKU_ID, S.DESCR AS DESCR,S.PACK_ID AS PACK_ID,S.SKU_GROUP AS SKU_GROUP,S.DESCR2 AS DESCR2,S.UDF2 AS UDF2, SUM(PD.QTY) AS QTY, ");
                strSql.Append(" PD.LOT AS LOT,LA.EXTERNAL_LOT AS EXTERNAL_LOT,LA.PRODUCE_DATE AS PRODUCE_DATE,LA.EXPIRY_DATE AS EXPIRY_DATE,LA.VENDOR_ID AS VENDOR_ID,LA.RECEIPT_DATE AS RECEIPT_DATE,LA.LOT_ATTR01 AS LOT_ATTR01,LA.LOT_ATTR02 AS LOT_ATTR02,LA.LOT_ATTR03 AS LOT_ATTR03,LA.LOT_ATTR04 AS LOT_ATTR04,LA.LOT_ATTR05 AS LOT_ATTR05,LA.LOT_ATTR06 AS LOT_ATTR06,LA.LOT_ATTR07 AS LOT_ATTR07,LA.LOT_ATTR08 AS LOT_ATTR08,LAC.DESCR AS LOT_ATTR09,LA.LOT_ATTR10 AS LOT_ATTR10 ");
                strSql.Append(" FROM  @_@INTRANSIT_DETAIL  ID");
                strSql.Append(" LEFT JOIN @_@INTRANSIT I ON ID.INTRANSIT_ID=I.INTRANSIT_ID");
                strSql.Append(" LEFT JOIN @_@SKU S ON S.SKU_ID=ID.SKU_ID AND S.OWNER_ID=ID.OWNER_ID");
                strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG SRCDC ON I.SRC_WH_DB_ID=SRCDC.WH_DB_ID");
                strSql.Append(" LEFT JOIN SYSADM.DB_CONFIG DESTCDC ON I.DEST_WH_DB_ID=DESTCDC.WH_DB_ID ");
                strSql.Append(" LEFT JOIN @_@PICK_DETAIL PD ON I.ORDER_ID =PD.ORDER_ID AND ID.ORDER_LINE_ID=PD.ORDER_LINE_ID");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT ");
                strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'  WHERE PD.STATUS ='Shipped' ");
                StrGroupby = "GROUP BY SRCDC.WH_DB_DESC  ,DESTCDC.WH_DB_DESC ,I.OWNER_ID ,ID.SKU_ID, S.DESCR ,S.PACK_ID,S.SKU_CLASS_ID ,S.SKU_GROUP ,S.DESCR2 ,S.UDF2,PD.LOT, LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LA.RECEIPT_DATE,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LAC.DESCR,LA.LOT_ATTR10";
            }
            string sql = strSql.ToString().Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            sql = sql + StrGroupby;
            if (!warehouseTransferRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, warehouseTransferRptQueryEntity.CurrentPage, warehouseTransferRptQueryEntity.PageSize, warehouseTransferRptQueryEntity.SortField, warehouseTransferRptQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                sql = sql.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'") + " ORDER BY " + warehouseTransferRptQueryEntity.SortField + " " + warehouseTransferRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GlobalInventoryWithWHRptDataSet(List<string> sqlWhere, GlobalInventoryWithWHRptQueryEntity globalInventoryWithWHRptQueryEntity, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int i;
            string strSqlWhere = string.Empty;
            for (i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSqlWhere = strSqlWhere + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    strSqlWhere = strSqlWhere + " AND " + sqlWhere[i].ToString();
                }
            }
            StringBuilder strSelectSql = new StringBuilder();
            strSelectSql.Append(" SELECT WH_ID,OWNER_ID,SKU_ID,SKU_DESCR,SKU_DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR, SUM(QTY) AS QTY ,UOM,SUM(OK_QTY) AS OK_QTY,SUM(BAD_QTY) AS BAD_QTY,SUM(DAMAGE_QTY) AS DAMAGE_QTY, SUM(HOLD_QTY) AS HOLD_QTY ,SUM(ALLOCATED_QTY) ALLOCATED_QTY,SUM(AVAILABLE_INVENTORY) AS AVAILABLE_INVENTORY,SUM(TOTLE_WGT) AS TOTLE_WGT,SUM(TOTLE_CUBE) AS TOTLE_CUBE,(SELECT MAX(TRANS_DATE) FROM @_@INV_TRANS WHERE LOT=LOT AND TRANS_TYPE+SOURCE_TRANS_TYPE='INRECEIPTDETAILADD') AS INBOUND_DATE, (SELECT MAX(TRANS_DATE) FROM @_@INV_TRANS WHERE LOT=LOT AND TRANS_TYPE='OT') AS OUTBOUND_DATE ");
            if (globalInventoryWithWHRptQueryEntity.SHOW_LOT == "Y")
            {
                strSelectSql.Append(" ,VENDOR_ID,EXTERNAL_LOT,PRODUCE_DATE  ");
            }
            else
            {
                strSelectSql.Append(" ,''AS VENDOR_ID,'' AS EXTERNAL_LOT,'' AS PRODUCE_DATE  ");
            }
            strSelectSql.Append(" FROM ( ");
            for (i = 0; whLoginID.Count > i; i++)
            {
                strSelectSql.Append(" SELECT  F.WH_DB_DESC AS WH_ID,A.OWNER_ID, A.SKU_ID,B.DESCR AS SKU_DESCR,B.DESCR2 AS SKU_DESCR2,B.SKU_CLASS_ID,C.DESCR AS SKU_CLASS_DESCR, SUM(A.QTY) as qty,D.PACK_UOM1 AS UOM, CASE E.LOT_ATTR09+A.STATUS WHEN 'OKOK' THEN SUM(A.QTY) ELSE 0 END AS OK_QTY, CASE E.LOT_ATTR09+A.STATUS WHEN 'HOLDOK' THEN SUM(A.QTY) ELSE 0 END  AS BAD_QTY , CASE E.LOT_ATTR09+A.STATUS WHEN 'DAMAGEOK' THEN SUM(A.QTY) ELSE 0 END AS DAMAGE_QTY,  CASE A.STATUS WHEN 'HOLD' THEN SUM(A.QTY) ELSE 0 END AS HOLD_QTY,SUM(A.ALLOCATED_QTY)AS ALLOCATED_QTY , SUM(CASE A.STATUS WHEN 'HOLD' THEN 0 ELSE CASE CASE E.LOT_ATTR09+A.STATUS WHEN 'OKOK' THEN A.QTY ELSE 0 END WHEN 0 THEN 0 ELSE CASE E.LOT_ATTR09+A.STATUS WHEN 'OKOK' THEN A.QTY ELSE 0 END - A.ALLOCATED_QTY - CASE A.STATUS WHEN 'HOLD' THEN A.QTY ELSE 0 END END END) AS AVAILABLE_INVENTORY, E.VENDOR_ID,E.EXTERNAL_LOT,E.PRODUCE_DATE,SUM(B.GROSS_WGT*A.QTY) AS TOTLE_WGT ,SUM(B.[CUBE]*A.QTY) AS TOTLE_CUBE  ");
                strSelectSql.Append(" FROM @_@INV_LOT_LOC_LPN A   ");
                strSelectSql.Append(" LEFT JOIN @_@SKU B  ON A.OWNER_ID =B.OWNER_ID AND A.SKU_ID = B.SKU_ID   ");
                strSelectSql.Append(" LEFT JOIN @_@SKU_CLASS C ON B.SKU_CLASS_ID=C.SKU_CLASS_ID AND B.OWNER_ID = C.OWNER_ID             ");
                strSelectSql.Append(" LEFT JOIN @_@PACK D ON B.PACK_ID= D.PACK_ID  ");
                strSelectSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE E ON A.LOT=E.LOT ");
                strSelectSql.Append(" INNER JOIN SYSADM.DB_CONFIG F ON  A.WH_ID = F.DB_LOGIN_ID  AND F.IS_GLOBAL='N' ");
                strSelectSql.AppendFormat(" {0} ", strSqlWhere);
                strSelectSql.Append("  GROUP BY F.WH_DB_DESC,A.LOT,A.OWNER_ID,A.SKU_ID,B.DESCR ,B.DESCR2 ,B.SKU_CLASS_ID,C.DESCR ,D.PACK_UOM1,E.LOT_ATTR09,A.STATUS,E.VENDOR_ID,E.EXTERNAL_LOT,E.PRODUCE_DATE ");
                if (whLoginID.Count > (i + 1))
                {
                    strSelectSql.AppendFormat(" UNION ALL ", new object[0]);
                }
                strSelectSql = strSelectSql.Replace("@_@", whLoginID[i]);
            }
            strSelectSql.Append(" ) temp group by WH_ID,OWNER_ID,SKU_ID,SKU_DESCR,SKU_DESCR2,SKU_CLASS_ID,SKU_CLASS_DESCR,UOM ");
            if (globalInventoryWithWHRptQueryEntity.SHOW_LOT == "Y")
            {
                strSelectSql.Append(" ,VENDOR_ID,EXTERNAL_LOT,PRODUCE_DATE  ");
            }
            string StrSql = strSelectSql.ToString();
            if (!globalInventoryWithWHRptQueryEntity.IsGetAll)
            {
                StrSql = PagingHelper.GetPagingSQL(strSelectSql.ToString(), globalInventoryWithWHRptQueryEntity.CurrentPage, globalInventoryWithWHRptQueryEntity.PageSize, globalInventoryWithWHRptQueryEntity.SortField, globalInventoryWithWHRptQueryEntity.SortDirection);
            }
            else
            {
                StrSql = StrSql + " ORDER BY " + globalInventoryWithWHRptQueryEntity.SortField + " " + globalInventoryWithWHRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, StrSql);
        }
    }
}

